Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 21, 2015

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

By Arshad Ali

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM