question

nitink avatar image
nitink asked

get comma seperated results from query

Hi All, I have a query to make which i m finding difficult to make. I have a table with following structure: Id Field 1 'aaaa' 1 'bbbb' 2 'cccc' 2 'dddd' I want the output from the query as follows: id fields 1 'aaaa','bbbb' 2 'cccc','dddd' CREATE TABLE #a ( id INT , field varchar(1000) ) INSERT INTO #a ( id, field ) VALUES ( 1, -- id - int 'aaaaa' -- field - varchar(1000) ) INSERT INTO #a ( id, field ) VALUES ( 1, -- id - int 'bbbb' -- field - varchar(1000) ) INSERT INTO #a ( id, field ) VALUES ( 2, -- id - int 'cccc' -- field - varchar(1000) ) INSERT INTO #a ( id, field ) VALUES ( 2, -- id - int 'ffff' -- field - varchar(1000) ) Please help me ..... Regards, Nitin
sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
nitink avatar image
nitink answered
got it right finally .... SELECT DISTINCT id, field= STUFF((SELECT TOP 100 PERCENT ',' + x.field FROM #a x WHERE x.Id = t.Id FOR XML PATH('')), 1, 1, '') FROM #a t group BY id
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.