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.

Scenario:
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
Get Comma separated List


I want output like:



Get Comma separated List
Get Comma separated List


I come up with very good solution. Let me share with all of you:

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

Get Comma separated List


My Output will be:







Get Comma separated List

Get Comma separated List


Please make comments, if this helps you in any way

Posted by at 11:46 PM
Labels: , , , , , , ,

30 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 for the post.
    ReplyDelete
  16. Thanks Tejas. This was extremely helpful for me.

    Kind Regards
    Bernhard
    (South Africa)
    ReplyDelete
  17. Hi Tejas This is Extremly Fine.
    ReplyDelete
  18. This is exactly what i needed. Thank You.
    ReplyDelete
  19. 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
  20. Thanks.. This is what I searched for..
    ReplyDelete
  21. Thanks man. This is what I was looking for and using variables wasn't an option for us.
    ReplyDelete
  22. This is so cool...I struggled so much with coalese
    ReplyDelete
  24. Nice Article ... Very helpful ... Brief & to the point ...
    ReplyDelete
  25. How can this be done by using coalesce ?
    ReplyDelete
  26. 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
  27. Hi Chirag,

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

    Tejas
    SQLYoga
    ReplyDelete
  28. superb, very useful information . 10/10 :)
    ReplyDelete

Subscribe to: Post Comments (Atom)