Rows flattened to concatenated string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rows flattened to concatenated string

Author

Topic

dougiel
Starting Member

4 Posts

Posted - 2008-06-20 : 08:44:11

I have a table with multiple rows for a single reference, e.g.

Col1 Col2
1 John
1 Mary
1 Tom
2 Dick
2 Anne

How do I create this view:

Col1 Col2
1 John, Mary, Tom
2 Dick, Anne

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-20 : 08:49:49

I forget who I stole this code from, probably Viskh and/or Peso - this looks like Peso's coding--, this question is often asked and you can find many threads on it using the search function here.

DECLARE @Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID
ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID
FOR XML PATH('')
), 1, 1, ''
) AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 09:43:09

quote:
Originally posted by jimf

I forget who I stole this code from, probably Viskh and/or Peso - this looks like Peso's coding--, this question is often asked and you can find many threads on it using the search function here.

DECLARE @Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID
ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID
FOR XML PATH('')
), 1, 1, ''
) AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

Jim

This is Peso's

. I mostly use the CROSS APPLY approach.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-20 : 10:08:08

That's what I thought. I'm having trouble wrapping my head around cross apply, so I went with Peso's. I've stolen quite a bit of your code too so it's hard to keep track!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 10:15:48

quote:
Originally posted by jimf

That's what I thought. I'm having trouble wrapping my head around cross apply, so I went with Peso's. I've stolen quite a bit of your code too so it's hard to keep track!

Cool

. I've not made them myself either. i also learn the new methods from others. Way to go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 18:16:22

quote:
Originally posted by jimf

That's what I thought. I'm having trouble wrapping my head around cross apply, so I went with Peso's. I've stolen quite a bit of your code too so it's hard to keep track!

FYI, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources