h1

SQL SERVER: Get column values as comma seperated list using XML PATH() instead of UDF’s using SQL COALESCE

02/28/2009

Today I have the following situation, where I need to display all related data in comma seprated 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:

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

Advertisements

52 comments

  1. It was very helpful and complete. Thanks


  2. awesome .. was banging my head for days … !!!! thanks


  3. Hi..,

    Very very helpful…

    What I was doing is creating a function for this and using it in the select query.

    Select field1, [dbo].[GetCommaSeparatedNames](field1) from #test

    here is the function

    CREATE FUNCTION [dbo].[GetCommaSeparatedNames]
    (
    @Field1 as int
    )
    RETURNS VarChar(8000)
    AS
    BEGIN

    Declare @F2 VarChar(8000)
    select @F2 =@F2 + ‘,’ + Field2 from #test
    where field1=@Field1

    — Return the result of the function
    RETURN SubString(@F2 ,2,Len(@F2))

    END


  4. Hi,

    Yes that is also the alternate way but costly way in terms of performance.

    Tejas


  5. Hi,
    This simple query will help.

    DECLARE @SQL AS VARCHAR(500)

    SELECT @SQL ISNULL(@SQL ‘ ‘)+’ ‘ + ColumnName FROM TableName

    SELECT @SQL


  6. Hi,

    I could not get idea what you want to do with this select statement.

    Could you please explain your idea? So we can share it with others too

    It will just give me values with space separated.

    Thanks,

    Tejas


  7. This is a big help, but have a question.

    Original Data
    Id Letter
    1 A
    1 B
    1 C
    2 X
    2 Y

    Statement works great returning

    1 A,B,C
    2 X,Y

    Which we use to create a file for another system that in turn makes changes to the data and returns a file such as:

    1 A,C,D
    2 X,Y,Z,1,2,3

    The trouble we’re having is taking this result and getting it back to its original structure

    1 A
    1 C
    1 D
    2 X …..

    Any help would be appreciated.
    Thanks


  8. Hi Robert,

    Your question is Interesting. We always need to the way to make rows from comma separated List.

    I already wrote an article to do so. This will separate it out one comma separated list into rows. You can use this in your way.

    Please check: http://www.sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html

    Tejas
    SQL Yoga


  9. Thanks a lot for sharing your SQL expertise. I really helped me in developing something real quick…

    Thanks again!!


  10. hi Tejas Usefull article. thanks for that.

    could you give us an example if we need 2 columns from 2 different tables (inner joined)


  11. Hi Lavan,

    If you want to make Comma Separated lists from multiple tables you can write the same query as:

    SELECT field1,
    SUBSTRING(
    (
    SELECT ( ‘ ,’ + t2.field2 + ‘,’ + )
    FROM #test t2
    INNER JOIN ON
    WHERE t1.Field1 = t2.Field1
    ORDER BY t2.Field2
    FOR XML PATH(”)
    ), 3, 1000)
    FROM #test t1
    GROUP BY field1

    By This way, we can generate list from multiple tables too.

    Let me know if it helps you. Let us know your questions too.

    Tejas,
    http://www.SQLYoga.com


  12. useful article


  13. i would like to know how to concantenate 2 columns using the For XML Path.

    current results:

    Date Place Customer SONumber
    10/1 ABC TestCust 123
    10/1 DEF TestCust 123
    10/1 DEF TestCust 456

    needed results:

    Date Place Customer SONumer
    10/1 ABC/DEF TestCust 123/456

    could someone help me with this? thanks.


  14. Does it support special character in data like @&><% using "for xml path" I tried follwoing stuff.

    CREATE TABLE #test(
    field1 VARCHAR(5), field2 VARCHAR(5)
    )

    INSERT INTO #test
    SELECT '001','A#AA'
    UNION ALL
    SELECT '001','BB*B'
    UNION ALL
    SELECT '002','C’
    UNION ALL
    SELECT ‘004’,’@E&EE’
    UNION ALL
    SELECT ‘004’,’!FFF’
    UNION ALL
    SELECT ‘004’,’GGG?’
    UNION ALL
    SELECT ‘004’,’@EE&E’

    Select Field1,
    Substring(
    ( Select(‘, ‘ + Field2)
    From #test t2
    where t1.field1 = t2.field1
    ORder by Field1,field2 for xml path ( ” )),3,1000)
    From #test t1 Group by field1

    But I am getting following result

    001 A#AA, BB*B
    002 C<CC
    003 DDD>
    004 !FFF, @E&EE, @EE&E, GGG?


  15. Great article. Suggestion: Use “STUFF” instead of substring in case you do not knowt he length of the final comma separated string.
    Thanks!


  16. Thanks mate, that was exactly I needed. cheers


  17. Hi Is there a way to get all the rows values into a single row except NULL and empty values.


  18. Hi Anand,

    You just need:

    SELECT ( ‘,’ + field2)
    FROM #test t2
    WHERE NULLIF(t2.field2,”) IS NOT NULL
    ORDER BY t2.Field1
    FOR XML PATH(”)

    Check it out and let me know

    Thanks,
    Tejas
    SQLYoga.com


  19. Hi Tejas,Thank you It worked well.


  20. Hi ,
    Can we get the Running Totals in Pivot table.?


  21. Thanks mate…


  22. You are amazing. It would take me some time to digest how you made such a use of XML path here. Though it worked really well for my situation


  23. Great. Now, how can you select into separate columns instead of one comma-separated column ?
    Something like

    column1 colun2 column3 column4
    ———————————————–
    001 AAA BBB
    002 CCC
    003 DDD
    004 EEE FFF GGG

    Thank you


  24. How can you select into separate columns instead of into one comma-separated column:

    Col1 Col2 Col3 Col4
    001 AAA
    002 BBB CCC
    003 DDD
    004 EEE FFF GGG


  25. how do u split the comma separated values and output to xml
    eg.
    002

    column2
    BBB

    column2
    CCC


  26. Thanks Its very help full..but i am having a little problem..I have to get the output as

    Dimension Cartons
    30.5*24.5*48.5 47
    40.5*24.7*34.5 37,225,401

    but after executing your quey now i am getting output as

    Dimension Cartons
    30.5*24.5*48.5 ,112,47,9
    40.5*24.7*34.5 ,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.,147,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,225,3,3,37,401,7,7

    i am getting like this…please help me to rectify this.

    Thanks in Advance


  27. Hi Arfan,

    Can you please provide the input data? I would like to review how is it stored and will try to give you solution for the same.

    Thanks,
    Tejas
    SQLYoga.com


  28. Thank you for your reply. This is my input data

    Dimension Cartons

    47.1 X 42.3 X 67.5 1
    47.1 X 42.3 X 67.5 5
    47.1 X 42.3 X 67.5 9
    83.5 X 47.5 X 44.5 2-4
    83.5 X 47.5 X 44.5 6-8

    I have to get the output as

    Dimension Cartons

    47.1 X 42.3 X 67.5 1, 5, 9
    83.5 X 47.5 X 44.5 2-4, 6-8

    like this i need the output. After executing your query I am getting this 2 distinct dimensions but the cartons are not displaying properly. I am getting like this instead of what i need.

    Dimension Cartons
    47.1 X 42.3 X 67.5 ,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 – 2,1 – 2,1 – 4,10,100,100,10-14,115,117,1-2,1-2,1-2,1-2,1-2,1-2,1-2,1-2,12-13,12-13,1-3,13-17,14,1-4,148,15,1-5,152,16,16,165,17,17,17-18,18,18-19,184,19,19,19,19-20,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2 – 3,2 – 3,2 – 3,2 – 3,2.,20,21,23,2-3,2-3,2-3,2-3,2-3,2-3,2-3,2-3,234,24,24-25,25,25,27,299,3,3,3,3,3,3,3,3,3,3.,31,31-32,32,33,3-4,3-4,3-4,345,348,354-355,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4.,424,426,44,4-5,48,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,51,56-57,6,62,62,65,7,7,7 TO 8,7.,75,76,8,81,81,8-12,89,9,9,9,9,94,99-106

    83.5 X 47.5 X 44.5 ,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.,1-2,1-2,1-2,1-2,1-2.,1-2.,1-24,13,1-5,19-30,2,2,2,2,2,2,2,2,2,2,2,2,20-24,21,21,21,21,21,21,2-3,2-3,24,24,2-4,25-26,29,29,3,3,3,3,3,3,3,3,3.,3.,30,32,3-4,3-6,4,4-18,419,4-5,47,4-8,5,5,5,5,5,5-29,6,6-7,6-8,7,78,7-8,7-9,8,8-12,99

    why i am getting this much 1 series and 2 series. Please help me to rectify.


  29. Hi Arfan,

    Can you please post the query that you have used to generate the expected result? I have write a query for the same and able to get the expected result. Please find my query as below:

    DECLARE @SqlYoga TABLE(Dimension VARCHAR(50), Val VARCHAR(MAX))

    INSERT INTO @SqlYoga(Dimension, Val)
    SELECT '47.1 X 42.3 X 67.5', '1'
    UNION ALL
    SELECT '47.1 X 42.3 X 67.5', '5'
    UNION ALL
    SELECT '47.1 X 42.3 X 67.5', '9'
    UNION ALL
    SELECT '83.5 X 47.5 X 44.5', '2-4'
    UNION ALL
    SELECT '83.5 X 47.5 X 44.5', '6-8'

    --SELECT * FROM @SqlYoga

    SELECT Dimension,
    SUBSTRING(
    (
    SELECT ( ', ' + Val)
    FROM @SqlYoga t2
    WHERE t1.Dimension = t2.Dimension
    ORDER BY t1.Dimension
    FOR XML PATH('')
    ), 3, 1000)
    FROM @SqlYoga t1
    GROUP BY Dimension

    Let me know if it helps you or not.

    Thanks,
    Tejas
    SQLYoga.com


  30. I am fetching the data from a stored table. I need the output to be displayed like you said..in this query Val what it means..herewith i am posting my query

    select s1.Dimension,
    SUBSTRING(
    (
    select (‘,’ + Cartons) from [Sales Comment Line] t2
    where t1.Dimension = t2.Dimension
    order by
    Dimension,
    Cartons
    for XML PATH (”)),3,1000) as carton
    from [Sales Comment Line] t1 where No_ = ‘PEXP1213-137’ group by Dimension

    this is the one. I dont know what i am missing.


  31. Instead of Val i am using Cartons..i am having the field name as Cartons….but still


  32. Hi Arfan.

    Your query looks ok and if I change it to use my temporary data, I got the expected result. Can you please send me a query that has sample data and can try to generate the expected result (as I did by Temporary table)?

    Thanks,
    Tejas
    SQLYoga.com


  33. This is the query to get the sample data

    select Dimension, Cartons from [Sales Comment Line] where No_ = ‘PEXP1213-137’

    i am getting from this is

    Dimension Cartons

    47.1 X 42.3 X 67.5 1
    83.5 X 47.5 X 44.5 2-4
    47.1 X 42.3 X 67.5 5
    83.5 X 47.5 X 44.5 6-8
    47.1 X 42.3 X 67.5 9

    and the query i am using for the result i need is

    select t1.Dimension,
    SUBSTRING(
    (
    select (‘,’ + Cartons) from [Sales Comment Line] t2
    where t1.Dimension = t2.Dimension
    order by
    Dimension,
    Cartons
    for XML PATH (”)),3,1000) as carton
    from [Sales Comment Line] t1 where No_ = ‘PEXP1213-137’ group by Dimension

    Please help to get the result what i need


  34. Hi Arfan,

    You need to add “WHERE” condition on subquery too:

    select t1.Dimension,
    SUBSTRING(
    (
    select (‘,’ + Cartons) from [Sales Comment Line] t2
    where t1.Dimension = t2.Dimension
    AND No_ = ‘PEXP1213-137’
    order by
    Dimension,
    Cartons
    for XML PATH (”)),3,1000) as carton
    from [Sales Comment Line] t1
    WHERE No_ = ‘PEXP1213-137’ group by Dimension

    Can you add that and check? I hope this will help you to get expected result.

    Thanks,
    Tejas
    SQLYoga.com


  35. Thank you..Thank you so much i got the result. I just check that condition in the place as you said. and there is one more little problem .. I am getting like this

    Dimension Cartons
    47.1 X 42.3 X 67.5 ,5,9
    83.5 X 47.5 X 44.5 -4,6-8

    instead of this

    Dimension Cartons
    47.1 X 42.3 X 67.5 1,5,9
    83.5 X 47.5 X 44.5 2-4,6-8

    if i use XML PATH (”)),2,1000) then i am getting the result what i need. Is it right to use like this.

    Thank you so much…Thanks a lot


  36. Hi Arfan,

    Please use

    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

    Change is marked in BOLD.

    Thanks,
    Tejas
    SQLYoga.com


  37. SELECT ( ‘, ‘ + field2) in sub query, has space after “, “


  38. hi…Affan again..i am getting the result as

    Dimensions Cartons

    47.3 X 42.3 X 52.8 19
    61 X 46 X 53 18
    61.5 X 48.2 X 68 1, 2, 3-17

    is it possible to display the cartons as asc order

    Dimensions Cartons
    61.5 X 48.2 X 68 1, 2, 3-17
    61 X 46 X 53 18
    47.3 X 42.3 X 52.8 19

    and my query is

    select s1.Dimension,
    SUBSTRING(
    (
    select (‘, ‘ + Cartons) from [Auro Lab$Sales Comment Line] s2
    where s1.Dimension = s2.Dimension
    and No_ = ‘PEXP1213-540’
    order by
    Cartons,
    Dimension
    for XML PATH (”)),3,1000) as carton
    from [Auro Lab$Sales Comment Line] s1 where No_ = ‘PEXP1213-540’ group by Dimension

    Please help me to rectify


  39. Hi Arfaan,

    yes sure, just add ORDER BY to your query and you are done.

    Tejas
    SQLYoga.com


  40. Hi..need your help again…i am having the result as

    a1 – 300nos
    a2 – 200nos
    a3 – 200nos , a1 – 200nos and am i getting the sum of this too as..

    a1 – 500nos
    a2 – 200nos
    a3 – 200nos

    now i need to split this hyphen(-) in sql.. Please help me to rectify


  41. Hi Arfaan,

    For this you need to build Stored procedure and need to process each row separately. Can you please do the same and populate dummy table to get expected output? Let me know if I can help you.

    Thanks,
    Tejas
    SQLYoga.com


  42. Thanks alot.. Very useful and it is optimized solution in terms of performance.


  43. Hi Affan again..i am having 3 columns

    through your query i am getting the result as

    Dimension Declaration carton
    34 X 34.3 X 35.7 a1 – 2800 Nos 1, 2-8, 9
    34 X 34.3 X 35.7 a2 – 345 Nos 1, 2-8, 9
    34 X 34.3 X 35.7 a3 – 240 Nos 1, 2-8, 9

    but i need the result as

    dimension declaration cartons
    34 X 34.3 X 35.7 a1 – 2800 Nos 1
    34 X 34.3 X 35.7 a2 – 345 Nos 2-8
    34 X 34.3 X 35.7 a3 – 240 Nos 9

    this is my query…can you guide me
    select distinct s1.Dimension, s1.Declaration ,SUBSTRING(( select (‘, ‘ + Cartons) from [Sales Comment Line] s2 where s1.Dimension = s2.Dimension
    and No_ = ‘PEXP1213-592’ order by Cartons,Dimension for XML PATH (”)),3,1000) as carton
    from [Sales Comment Line] s1 where No_ = ‘PEXP1213-592’ order by carton


  44. hi…its not needed..i did what i need…thanks


  45. its very Nice and use Ful,
    I had to loop some 1000 rows to get but with this Query no need of att That!!!!


  46. Awesome…This helped me a lot and saved my time..thanks a lot


  47. Good Article. I have a question. In the above scenario, how can we eliminate duplicate values. Say if field2 is having duplicate values i just need to display unique values alone. Can you please help me?


  48. Hi Adarsh,

    To remove duplicates from string, you can write query as follows:

    SELECT field1,
    SUBSTRING(
    (
    SELECT ( ‘, ‘ + t2.field2)
    FROM #test t2
    WHERE t1.Field1 = t2.Field1
    GROUP BY t2.Field2
    ORDER BY t2.Field2
    FOR XML PATH(”)
    ), 3, 1000)
    FROM #test t1
    GROUP BY field1

    Let me know if it helps.

    Tejas


  49. Thanks a lot Tejas. It worked !


  50. It can be doene using the STUFF finction easily:-

    SELECT ReportId,
    STUFF((SELECT ‘, ‘ + Email
    FROM your_table b
    WHERE b.ReportId = a.ReportId
    FOR XML PATH(”)), 1, 2, ”)
    FROM your_table a
    GROUP BY ReportId


  51. Thanks. It’s really helpful.


  52. thanks bro it helped me very much…..



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: