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

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



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)

CREATE TABLE Fruit

(

NAME VARCHAR(100)

)


INSERT Fruit(NAME) VALUES('Apple,Banana,Orange')

   

We can use STRING_SPLIT function which takes a string that should be divided and the separator that will be used to divide string. It returns a single-column table with substrings.

SELECT STRING_SPLIT(NAME,',') FROM Fruit

   

NOTE: The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

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

 

SELECT STUFF((SELECT ',' + Name FROM Fruits FOR XML PATH ('')),1,1,'') as Fruits