Combing 2 Rows Into 1 With Same ID? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Combing 2 Rows Into 1 With Same ID?

Author

Topic

novedturn
Starting Member

1 Post

Posted - 2011-01-28 : 16:35:31

Here is a sample of my table:

ID Name Codes
1 John D S12, S13, S89
1 John D S98, T12
2 Joe D X27, S28
3 Bob S F12, F13, X43
3 Bob S S44, X38, S90
4 Steve F D25

I want a query that will return the following

ID Name Codes
1 John D S12, S13, S89, S98, T12
2 Joe D X27, S28
3 Bob S F12, F13, X43, S44, X38, S90
4 Steve F D25

Any ideas?

djorre
Yak Posting Veteran

94 Posts

Posted - 2011-01-29 : 04:05:58

Execute this and see what you can use, code by Peso:

-- Prepare sample data
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources