Monday, February 23, 2009

Create A Comma Delimited List From a Column

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id Names
1 A
1 B
1 C
2 A
2 B
3 X
3 Y
3 Z

And here is the expected output -

Id AllNames
1 A, B, C
2 A, B
3 X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table
CREATE TABLE Test
(
Id
INT,
Names VARCHAR(100)
)
GO
-- Load sample data
INSERT INTO Test SELECT
1,'A' UNION ALL SELECT
1,'B' UNION ALL SELECT
1,'C' UNION ALL SELECT
2,'A' UNION ALL SELECT
2,
'B' UNION ALL SELECT
3,'X' UNION ALL SELECT
3,'Y' UNION ALL SELECT
3,
'Z'
GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id 
           ,AllNames = SubString (( SELECT ', ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id
           ,AllNames = REPLACE (( SELECT T2.Names AS
'data()'
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), ' ', ', ')
FROM Test as T1
GROUP BY
Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id 
           ,AllNames = SubString (( SELECT '| ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

Mangal Pardeshi

SQL MVP

9 comments:

  1. Very nice Mangal,

    I am big fan of your writing as well your help in MSDN.

    Best Regards,
    Pinal Dave

    ReplyDelete
  2. Thanks Pinal. It's coming from you, I'm very glad. still learning, and to be honest you are one of my inspiration when it's come to blog writing.

    ReplyDelete
  3. How to do a reverse of this..

    ReplyDelete
  4. Hi Archana,

    What do you mean by reverse of it?
    If you are looking for creating Comma Delimited string from row values then you can check my this blog entry - http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html

    Thanks
    Mangal

    ReplyDelete
  5. Very Nice. Thanks Mangal

    ReplyDelete
  6. If a string contains a "&" character, it is output as "&amp"... how do we avoid this?

    ReplyDelete
  7. Nice Solution,
    Thanks!!!

    ReplyDelete
  8. Thanks A lot Mangal!! Wonderful solution

    ReplyDelete
  9. hi Mangal i have used the XML Path in one of my query but as data in the table is above 60,000 it's taking much time like 8 min

    This is my Query

    SELECT distinct Brand,Cust_ID, (SELECT distinct second_id+', ' FROM Sales t2 WHERE t2.Brand = t1.Brand AND t2.Cust_ID = t1.Cust_ID FOR XML PATH('')) AS prd_ID into SalReport FROM Sales t1 GROUP BY Brand,Cust_ID

    ReplyDelete