Turn Field Values into Coma Separated List

Question

Post reply

Turn Field Values into Coma Separated List

scotsditch

Ten Centuries

Points: 1020
More actions
August 11, 2015 at 3:28 pm

#299557

Hi I'm hoping somebody has a slick sql trick that can show me. I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Example 1:
field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g
and I would like to get it in the form
Example 2:
field1_field3
1_____a,b,c
2_____f,g

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Give this a try.

select distinct

mt.field1,

ca.field3

from

dbo.MyTable mt

cross apply (select stuff((select ',' + mt1.field2

from dbo.MyTable mt1

where mt1.field1 = mt.field1

for xml path(''),TYPE).value('.','varchar(max)'),1,1,''))ca(field3);

WayneS SSC Guru Points: 95460 More actions · Answer 2

Lynn Pettis (8/11/2015)
Give this a try.
select distinct
mt.field1,
ca.field3
from
dbo.MyTable mt
cross apply (select stuff((select ',' + mt1.field2
from dbo.MyTable mt1
where mt1.field1 = mt.field1
for xml path(''),TYPE).value('.','varchar(max)'),1,1,''))ca(field3);

And you can read all about how this works at this article[/url].

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2