SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 A query that combines data into one cell?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shanew
Starting Member

USA
20 Posts

Posted - 03/21/2013 :  01:17:04  Show Profile  Visit Shanew's Homepage  Send Shanew a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 03/21/2013 :  01:39:54  Show Profile  Reply with Quote

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


--
Chandu
Go to Top of Page

Shanew
Starting Member

USA
20 Posts

Posted - 03/21/2013 :  02:07:10  Show Profile  Visit Shanew's Homepage  Send Shanew a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/21/2013 :  02:09:54  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000