SQL Server Interview Questions and Answers: Converting Comma separated values to Rows and Vice Versa in SQL Server(Method 1).

Converting Comma separated values to Rows and Vice Versa in SQL Server(Method 1).

Introduction:

While doing ETL Testing or while Reporting you may encounter a situation where you will have comma separated (or separated with some other character) values and you want to report them in rows of a table or vice-versa.

In this article, I am going to explain how to write SQL query in SQL Server to handle this scenario.

Scenario: 1(Converting comma separated values in a cell to different rows)

For converting a comma separated value to rows (as shown in above picture), I have written a user defined function to return a table with values in rows. It takes comma separated values as the input parameter, iterates through it as long as it finds a comma in the value, takes each value before the comma, inserts into a table and finally returns the inserted data from that table.
It makes use of CHARINDEX inbuilt function to search for the existence of commas in the input parameter and returns the starting position of comma. It does as long as the comma position is greater than zero. Then it makes use of the STUFF inbuilt function to replace a part of the main input parameter (which has already been inserted into the table) with a zero length string; effectively removing the value before comma, which has already been extracted from the main input parameter and inserted into the table. It also uses LTRIM and RTRIM inbuilt functions to remove any extra spaces from the beginning or end of the value if there are any.

/*User Defined Function*/

CREATE FUNCTION dbo.BreakStringIntoRows (@CommaSeperatedString varchar(1000))

RETURNS @Result TABLE (Column1 VARCHAR(100))

BEGIN

DECLARE @DelimiterLocation INT

WHILE (CHARINDEX(',', @CommaSeperatedString, 0) > 0)

BEGIN

SET @DelimiterLocation = CHARINDEX(',', @CommaSeperatedString, 0)

INSERT INTO @Result (Column1)

/*LTRIM and RTRIM to ensure blank spaces are removed*/

SELECT RTRIM(LTRIM(SUBSTRING(@CommaSeperatedString, 0, @DelimiterLocation)))

SET @CommaSeperatedString = STUFF(@CommaSeperatedString, 1, @DelimiterLocation, '')

END

/*Below step is for inserting last value into the table since above part of query will not insert last record into the table*/

INSERT INTO @Result (Column1)

SELECT RTRIM(LTRIM(@CommaSeperatedString)) /*LTRIM and RTRIM to ensure blank spaces are removed*/

RETURN

END

/*Using the User Defined Function to convert comma separated values into rows*/

SELECT * FROM dbo.BreakStringIntoRows('Dog,Cat,Cow') --Without space

SELECT * FROM dbo.BreakStringIntoRows('Dog , Cat, Cow')

--With spaces in between the values

Scenario: 2(Converting Rows to Comma separated values)

Before explaining how to achieve this let me create the table and insert some values

/*Create a table containing values in rows*/

CREATE TABLE Animals
( Name VARCHAR(50))GO

/*Insert Values in the rows*/
INSERT Animals(Name) VALUES ('Dog')

INSERT Animals(Name) VALUES ('Cat')

INSERT Animals(Name) VALUES ('Cow') GO

Now we have values in rows and to convert them to a single comma separated value, we can use the script below, which uses the COALESCE inbuilt function.

The COALESCE function takes a list of parameters, separated by commas, evaluates them and returns the value of the first of its input parameters that is not NULL.

Though COALESCE and ISNULL functions have a similar purpose, they can behave differently. For example, ISNULL function is evaluated only once whereas the input values for the COALESCE function can be evaluated multiple times or until it reaches to the first not-NULL value to return.