Need query for comma delimited output

Question

Need query for comma delimited output

vignesh.ms

Hall of Fame

Points: 3456
More actions
January 29, 2014 at 4:44 am

#282891

Hi there,
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farlzy Mr or Mrs. 500 Points: 561 More actions · Answer 1

You're after something like this I guess? - Obviously this is rbar, you'd be better trying to adopt a set based approach using a pivot or something if you can, depending on what you're trying to do... This won't be speedy on a large data set.

Just as an FYI loads of people have looked at this post but nobody has replied. I'd have thought you're a lot more likely to get a response if you give it a go yourself and then ask for help with problems rather than asking people to write stuff for you?!?

drop table #sample

drop table #group

create table #sample

(name varchar(100),

id int)

insert into #sample values ('customerid','15339119')

insert into #sample values ('Title','15339119')

insert into #sample values ('firstname','15339119')

insert into #sample values ('prevcr','2146822710')

insert into #sample values ('currcr','2146822710')

insert into #sample values ('brandcode','2146822710')

select id, cast('' as varchar(1000)) as name

into #group

from #sample

group by id

declare @p1 varchar(1000)

declare @id int

declare c1 cursor

for select id from #group;

open c1

fetch next from c1 into @id

while @@fetch_status= 0

begin

set @p1 = '';

select @p1 = @p1 + name + ',' from #sample where id = @id

update #group

set name = substring(@p1, 1, len(@p1)-1)

where id = @id

fetch next from c1 into @id

end

close c1

deallocate c1

select * from #group

Lowell SSC Guru Points: 323518 More actions · Answer 2

great job posting sample data! post slike this make me want to help!

here's an example of a set based operation using a well know technique with FOR XML;

the STUFF simply takes off the preceeding comma.

/*

id(No column name)

15339119customerid, Title, firstname

2146822710prevcr, currcr, brandcode

*/

SELECT A.id

,STUFF(

(

SELECT ', ' + B.name

FROM #sample B

WHERE A.id = B.id

FOR XML PATH(''))

,

1, 2, '')

FROM #sample A

GROUP BY A.id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Farlzy Mr or Mrs. 500 Points: 561 More actions · Answer 3

Nice one Lowell, that's much more elegant than my solution!

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 4

Lowell (1/29/2014)
great job posting sample data! post slike this make me want to help!
here's an example of a set based operation using a well know technique with FOR XML;
the STUFF simply takes off the preceeding comma.
/*
id(No column name)
15339119customerid, Title, firstname
2146822710prevcr, currcr, brandcode
*/
SELECT A.id
,STUFF(
(
SELECT ', ' + B.name
FROM #sample B
WHERE A.id = B.id
FOR XML PATH(''))
,
1, 2, '')
FROM #sample A
GROUP BY A.id

Hi Lowell,

Thanks for your kind assistance..

I need another favor...

Im newbie to SQL, please explain how it works ??

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

vignesh.ms (1/30/2014)
Lowell (1/29/2014)
great job posting sample data! post slike this make me want to help!
here's an example of a set based operation using a well know technique with FOR XML;
the STUFF simply takes off the preceeding comma.
/*
id(No column name)
15339119customerid, Title, firstname
2146822710prevcr, currcr, brandcode
*/
SELECT A.id
,STUFF(
(
SELECT ', ' + B.name
FROM #sample B
WHERE A.id = B.id
FOR XML PATH(''))
,
1, 2, '')
FROM #sample A
GROUP BY A.id
Hi Lowell,
Thanks for your kind assistance..
I need another favor...
Im newbie to SQL, please explain how it works ??

Are you familiar with books online? Here is the topic about STUFF from there. http://technet.microsoft.com/en-us/library/ms188043.aspx

An explanation of how it works to generate a comma separated list as Lowell demonstrated can be found in Wayne Sheffield's excellent article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/