February 28, 2009

SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.
 
Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.
 
Example:
I have Table like:
CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)
Lets insert some data in this table:
INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'
So now my table has Data like:
Get Comma separated List
Expected Output:
Get Comma separated List
Proposed Solution:
SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1
Get Comma separated List
My Output will be:
Get Comma separated List
 
Please make comments, if this helps you in any way

59 comments:

  1. Thanks Mr.Shah

    This is very helpful article for me. It has solved my problem very easily.

    ReplyDelete
  2. That was exactly what i wanted.
    Simple and net.
    Good work and i'm glad to have found this script.
    Thanks

    ReplyDelete
  3. Super result Tejas. An amazingly simple solution.

    Many thanks.

    ReplyDelete
  4. Can I suggest a small change to eliminate the character conversion that can occur, for example if we add this row:
    '004','&&&'

    the output for 004 will be
    004 "&&&,EEE,FFF"

    To correct this, perhaps this way...

    select tbl.field1,
    substring(
    (
    SELECT ',' + field2 AS [text()]
    FROM #test as t
    WHERE t.field1 = tbl.field1
    ORDER BY field1, field2
    FOR XML PATH(''), type).value('(/text())[1]','varchar(max)'), 2, 1000)
    from #test as tbl
    group by tbl.field1

    this will yield

    004 "&&&,EEE,FFF"

    ReplyDelete
  5. Sorry the first output for the last comment should read...
    ---
    the output for 004 will be
    004 "&&&,EEE,FFF"

    ReplyDelete
  6. Does Not Work!
    Incorrect syntax near the keyword 'FOR'.

    ReplyDelete
  7. Hi,

    Please let me know Which version of SQL you are using?

    It should work with SQL 2005 and above

    Tejas

    ReplyDelete
  8. Absolutely elegant! I have have been doing this the hard way for years.

    ReplyDelete
  9. I also get the error - Incorrect syntax near the keyword 'FOR'.
    I'm using SQL 2000 which is probably the reason - darn as I really need this to work - any ideas?

    ReplyDelete
  10. Hi,

    SQL SERVER 2000 is not supporting XML DATATYPE.

    You can create one scalar function for the same, like:

    CREATE FUNCTION [dbo].[GetCommaSeparatedList]
    (
    @ID AS BIGINT
    )
    RETURNS VARCHAR(2000)
    AS
    BEGIN

    DECLARE @result VARCHAR(MAX)

    SELECT @result = COALESCE(@result + ', ', '') + ColumnName
    FROM [TableName]
    WHERE ID = @ID

    RETURN @result

    END

    and use this function as:

    SELECT [dbo].[GetCommaSeparatedList](1)

    That is work around that people use for SQL SERVER 2000.

    Thanks,

    Tejas Shah

    ReplyDelete
  11. Thanks Tejas for your suggestion to use scalar function. I'll give it a try.

    Christina

    ReplyDelete
  12. Hi Tejas,

    Thanks a lot for this post.Its very useful.

    I have a similar situation where I need to get a comma separated list of a column values but only the unique values .

    I have used your select query which is giving me below results : -

    column1 column2 column3 column4 column5 column6
    S09 E AO 21/10/2009 CRO CVT, DUB
    S09 E AO 21/10/2009 ITA DES, DES, DES, DES, DES, DES, SII, SII
    S09 E AO 23/10/2009 SWI INT, INT, INT, INT
    S09 E AO 28/10/2009 ITA DES, DES, DES, DES, DES, PSC

    I do get comma separated list for column6 but its repeating the duplicates .

    Is there any way to get rid of duplicate values for the column with XML Path query ?

    Please help me on this .

    Kind Regards

    Mandeep Bains

    ReplyDelete
  13. Hi Mandeep,

    You can do that with GROUP BY to identify UNIQUE names.

    Write a query as:

    SELECT field1,
    SUBSTRING(
    (
    SELECT ( ',' + t2.field2)
    FROM #test t2
    WHERE t1.Field1 = t2.Field1
    GROUP BY field2
    FOR XML PATH('')
    ), 2, 1000)
    FROM #test t1
    GROUP BY field1

    Thanks,

    Tejas

    ReplyDelete
  14. This is working but performance is poor when result set grows. I Compared both methods and "COALESCE(@result + ', ', '') + ColumnName" method is fast.

    ReplyDelete
  15. Thanks Tejas. This was extremely helpful for me.

    Kind Regards
    Bernhard
    (South Africa)

    ReplyDelete
  16. Hi Tejas This is Extremly Fine.

    ReplyDelete
  17. This is exactly what i needed. Thank You.

    ReplyDelete
  18. that was amazing; i have invested 2 hours on a report and used colase in the function to achieve the same what you did in a single query .. simple brilleant

    ReplyDelete
  19. Thanks.. This is what I searched for..

    ReplyDelete
  20. Thanks man. This is what I was looking for and using variables wasn't an option for us.

    ReplyDelete
  21. This is so cool...I struggled so much with coalese

    ReplyDelete
  22. Nice Article ... Very helpful ... Brief & to the point ...

    ReplyDelete
  23. How can this be done by using coalesce ?

    ReplyDelete
  24. Hi there , I want the result as following :

    Count
    -------------------------------------------------
    001 AA,BBB 2
    002 CC 1
    003 DD 1
    004 EE,FFF,GGG 3

    Any suggestions ???

    ReplyDelete
    Replies
    1. I was getting the missing characters too. The problem is the code was missing a space after the comma, i.e. SELECT ( ',' + field2) should be SELECT ( ', ' + field2)

      Note that there's no space in the text version (that you can copy and paste) but there is a space in the screenshot version just below it (at the time of this comment).

      Delete
  25. Hi Chirag,

    Can you please provide your data to generate the expected output?

    Tejas
    SQLYoga

    ReplyDelete
  26. superb, very useful information . 10/10 :)

    ReplyDelete
  27. LOVE YOU BOY :) ... It really worked for me. Dil chah raha ha pappi le looon aapki :)

    ReplyDelete
  28. very, very helpful - thank you ;-)

    ReplyDelete
  29. This is brilliant, thanks for sharing!

    ReplyDelete
  30. Thank you very much...

    ReplyDelete
  31. if the string is greater then 1000 length then what do we need to do

    ReplyDelete
  32. @Vaidaiah,

    You can specify up to 8000 characters with SUBSTRING function.

    Tejas
    SQLYoga.com

    ReplyDelete
  33. Hi,
    Is there any other way to implement the same logic?

    ReplyDelete
  34. Hi Maruthi,

    This looks simple and easy to use, so we are using it. Will publish a blog for the same, if I found a way to achieve it easier than this.

    Tejas
    SQL Yoga

    ReplyDelete
  35. Hi therе tο every onе, for the reasοn that I am genuinely
    kеen of reading this web site's post to be updated daily. It consists of good material.
    Here is my web blog : ipad priser

    ReplyDelete
  36. Thanks for this solution. It helped

    ReplyDelete
  37. QueueID From Emailid Stat

    Q123 jkm@yahoo.com JK@remotemail sent
    Q123 jkm@yahoo.com KKM@remotemail sent

    and i want output like
    QueueID From Emailid Stat
    Q123 jkm@yahoo.com JK@remotemail,KKM@remotemail sent,sent

    Please give me the exact query to get this output...

    Table name is "prl.whole_report"...

    ReplyDelete
  38. hey plz reply fast of that query for prl.whole_report i need it urgently....
    Rahul

    ReplyDelete
  39. Hi,

    You can use the same query as mentioned in the blog, http://sqlyoga.com/2009/02/sql-server-get-comma-separated-list.html.

    I have write an query for you, please find query as below:

    DECLARE @Test TABLE (QueueID VARCHAR(10), [From] VARCHAR(100), Emailid VARCHAR(100), Stat VARCHAR(100))

    INSERT INTO @Test(QueueID,[From],Emailid,Stat)
    SELECT 'Q123','jkm@yahoo.com','JK@remotemail','sent'
    UNION ALL
    SELECT 'Q123','jkm@yahoo.com','KKM@remotemail','sent'


    SELECT QueueID,
    SUBSTRING(
    (
    SELECT DISTINCT ( ', ' + t2.[From])
    FROM @Test t2
    WHERE t1.QueueID = t2.QueueID
    FOR XML PATH('')
    ), 3, 1000) AS [From],
    SUBSTRING(
    (
    SELECT DISTINCT ( ', ' + t2.Emailid)
    FROM @Test t2
    WHERE t1.QueueID = t2.QueueID
    FOR XML PATH('')
    ), 3, 1000) AS [Emailid],
    SUBSTRING(
    (
    SELECT ( ', ' + t2.Stat)
    FROM @Test t2
    WHERE t1.QueueID = t2.QueueID
    ORDER BY t2.Stat
    FOR XML PATH('')
    ), 3, 1000) AS [Status]
    FROM @Test t1
    GROUP BY QueueID

    Let me know if you have any question.

    Thanks,
    Tejas
    SQLYoga

    ReplyDelete
  40. I have send u just test data i have 64,000 records in table how can i run this query for whole table...
    rahul

    ReplyDelete
  41. Hi Rahul,

    Yes sure, you can run it. It might be slower, but lets see. Please keep us posted.

    Thanks,
    Tejas
    SQLYoga

    ReplyDelete
  42. Ya i can run it bt how can i union all the data that u have done using select query...
    rahul

    ReplyDelete
  43. Hi Rahul,

    You dont need to create Dummy table, as I did. I have created it just to have dummy data, so it will be easy to understand.

    You just need to run query with your table name as below:

    SELECT QueueID,
    SUBSTRING(
    (
    SELECT DISTINCT ( ', ' + t2.[From])
    FROM prl.whole_report t2
    WHERE t1.QueueID = t2.QueueID
    FOR XML PATH('')
    ), 3, 1000) AS [From],
    SUBSTRING(
    (
    SELECT DISTINCT ( ', ' + t2.Emailid)
    FROM prl.whole_report t2
    WHERE t1.QueueID = t2.QueueID
    FOR XML PATH('')
    ), 3, 1000) AS [Emailid],
    SUBSTRING(
    (
    SELECT ( ', ' + t2.Stat)
    FROM prl.whole_report t2
    WHERE t1.QueueID = t2.QueueID
    ORDER BY t2.Stat
    FOR XML PATH('')
    ), 3, 1000) AS [Status]
    FROM prl.whole_report t1
    GROUP BY QueueID

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  44. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@whole_report t2 WHERE t1.QueueID = t2.QueueID FOR XML PATH('') ), 3, 1000) AS ' at line 5

    I got error like above...
    rahul

    ReplyDelete
  45. Hi Rahul,

    Please use the query that I have posted.

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  46. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XML PATH('') ), 3, 1000) AS fname, SUBSTRING( ( SELECT DISTINCT ( ', ' + t2.ema' at line 7


    still i m getting the above error....
    rahul

    ReplyDelete
  47. actually i m getting error of xml path how can i remove that...
    rahul

    ReplyDelete
  48. Hi Rohul,

    Can you send me your query?

    Thanks,
    Tejas

    ReplyDelete
  49. SELECT QueueID,
    SUBSTRING(
    (
    SELECT DISTINCT ( ', ' + t2.fname)
    FROM whole_report t2
    WHERE t1.QueueID = t2.QueueID
    FOR XML PATH('')
    ), 3, 1000) AS fname,
    SUBSTRING(
    (
    SELECT DISTINCT ( ', ' + t2.emailid)
    FROM whole_report t2
    WHERE t1.QueueID = t2.QueueID
    FOR XML PATH('')
    ), 3, 1000) AS emailid,
    SUBSTRING(
    (
    SELECT ( ', ' + t2.stat)
    FROM whole_report t2
    WHERE t1.QueueID = t2.QueueID
    ORDER BY t2.stat
    FOR XML PATH('')
    ), 3, 1000) AS stat
    FROM whole_report t1
    GROUP BY QueueID

    ReplyDelete
  50. QueueID| From| Emailid| Stat

    Q123 | jkm@yahoo.com | JK@remotemail | sent
    Q123 | jkm@yahoo.com | KKM@remotemail| sent

    and i want output like
    QueueID | From | Emailid | Stat
    Q123 | jkm@yahoo.com | JK@remotemail,KKM@remotemail | sent,sent
    rahul

    ReplyDelete
  51. Youq query seems to be ok, please contact me at tejasnshah.it@gmail.com for further communication.

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  52. Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of Database Community.

    I have also prepared one article about, What should be our practice to store comma separated list in Database System.
    You can also visit my article, your comments and reviews are most welcome.
    http://www.dbrnd.com/2016/01/database-design-storing-a-comma-separated-list-in-a-database-is-a-bad-practice/

    ReplyDelete