Converting Comma Separated Value to Rows and Vice Versa in SQL Server

Introduction

Often while reporting you will encounter a situation where you will have comma separated (or separated with some other character) values in a single column but you want to report them in rows whereas in some other cases you might have values in multiple rows and want them to be a single value separated by comma or some other character. In this article, I am going to demonstrate how you can write queries in SQL Server to handle these scenarios quickly.  

Converting Comma Separated Value to Rows

For converting a comma separated value to rows, 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. It does that as long as the 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 table) with a zero length string; effectively removing the value before the comma, which has already been extracted from the main input parameter and inserted into the table. It also uses LTRIM and RTRIM functions to remove any extra spaces from the beginning or end of the value if there are any.

CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString   varchar(1000))
RETURNS   @Result TABLE (Column1   VARCHAR(100))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)      
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN 
END
GO
 
--Using the UDF to convert comma separated values into rows
SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange')
SELECT * FROM dbo.BreakStringIntoRows('Apple   ,    Banana,    Orange')

 

Result Screenshot

Converting Rows to Comma Separated Values

Before I go into detail and demonstrate converting rows into comma separated values, let’s first create a table and load some data with the script given below and as shown in the image.

CREATE TABLE Fruits
(
Name   VARCHAR(50)
)
GO
INSERT   Fruits(Name) VALUES ('Apple')
INSERT   Fruits(Name) VALUES ('Banana')
INSERT   Fruits(Name) VALUES ('Orange')
GO
SELECT * FROM Fruits
GO

 

Result Screenshot

 

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

DECLARE   @ConcatString VARCHAR(4000)
SELECT   @ConcatString = COALESCE(@ConcatString + ', ', '') + Name FROM Fruits 
SELECT   @ConcatString AS Fruits
GO

 

Result Screenshot

 

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.

In the above example, I have considered the comma as a character to separate values; though you can modify the above script to have any other character like $ or |, etc. for separating values.

Conclusion

In this article, I talked about how you can write a query using inbuilt SQL Server functions to convert comma separated values into rows or convert multiple rows values to be a single value separated by a comma. I have demonstrated using a comma for separating values but you can use any other character for value separation.

Resources

CONCAT and STUFF Functions in SQL Server 2012

COALESCE function

CHARINDEX function

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles