A query that combines data into one cell? - 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)

A query that combines data into one cell?

Author

Topic

Shanew
Starting Member

20 Posts

Posted - 2013-03-21 : 01:17:04

Hello,
I need a query that combines and displays all data found in one column separated with commas.
Example of data/table Name: TblX
ID Name Car
1 Jim Ford
2 Shane Toyota
2 Shane Chevy
2 Shane Nissan
3 Dan Chevy

Would like the output of the query for "where ID = 2" look like this
ID Name Cars
2 Shane Toyota, Chevy, Nissan

The Car field should list all cars belonging to ID 2 and have a , between them.

My attempt looked like this but no luck:
Select ID, Name, (select Car, ", " from TblX where ID = 2) AS Cars from TblX where ID = 2

Any ideas?
Thanks for the help!
Shane

Shane Weddle
www.TechKnowPros.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-21 : 01:39:54

[code]
DECLARE @TblX TABLE (ID INT, Name VARCHAR(10), Car VARCHAR(10))
INSERT INTO @TblX
SELECT 1 , 'Jim', 'Ford' UNION ALL
SELECT 2 , 'Shane', 'Toyota' UNION ALL
SELECT 2 , 'Shane', 'Chevy' UNION ALL
SELECT 2 , 'Shane', 'Nissan' UNION ALL
SELECT 3, 'Dan', 'Chevy'

SELECT ID, Name, STUFF( (SELECT ', '+Car FROM @TblX WHERE t.ID = ID FOR XML PATH('')), 1, 2, '') Cars
FROM @TblX t
GROUP BY ID, Name
[/code]

--
Chandu

Shanew
Starting Member

20 Posts

Posted - 2013-03-21 : 02:07:10

Hi Chandu,

Thanks but I had to edit the query to get it to work at all and even then it just returned all data in the table (nothing was combined)..

I had to remove the @'s to get it to work at all

SELECT [ID], [Name], STUFF( (SELECT ', '+Car FROM TblX WHERE t.ID = ID FOR XML PATH('')), 1, 2, '') Cars
FROM Tblx t
GROUP BY [ID], [Name]

Any ideas why it did not combine the data?

Thanks
Shane

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-21 : 02:09:54

quote:
Originally posted by Shanew

Hi Chandu,

Thanks but I had to edit the query to get it to work at all and even then it just returned all data in the table (nothing was combined)..

I had to remove the @'s to get it to work at all

SELECT [ID], [Name], STUFF( (SELECT ', '+Car FROM TblX WHERE t.ID = ID FOR XML PATH('')), 1, 2, '') Cars
FROM Tblx t
GROUP BY [ID], [Name]

Any ideas why it did not combine the data?

Thanks
Shane

That depends on your how your actual data is

Can you show your actual data and query you used against. if you have any other columns involved which you've not shown us so far, output will vary.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources