Returning data in a comma-delimited list

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

Returning data in a comma-delimited list

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2010-12-20 : 15:13:37

Here are my tables:

-----------------------------------------
Hotels
-----------------------------------------
HotelID | HotelName
-----------------------------------------

-----------------------------------------
HotelImages
-----------------------------------------
HotelImageID | HotelID | Filename
-----------------------------------------

I'd like to create a SELECT statement that will return the data like so:

HotelID | HotelName | Images
-----------------------------------------
1 | Hotel1 | Image1,Image2,Image3
2 | Hotel2 | Image4,Image5,Image6

How can I modify my query to do this? I have:

SELECT H.HotelID, H.HotelName, '' AS Images
FROM Hotels H

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-20 : 15:32:02

You can alter this code, stolen from I forget whom on this site, to suit your needs

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'

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

Everyday I learn something that somebody else already knew

Apples
Posting Yak Master

146 Posts

Posted - 2010-12-20 : 15:37:50

I forgot to mention that I'm using SQL Server 2000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources