Convert to CSV

Question

Convert to CSV

squvi.87

Default port

Points: 1450
More actions
June 2, 2016 at 3:55 am

#326715

Hi there,
Following code will do the job for me. But I wonder is there any other way to accomplish.
create table #t (id int , name varchar(100))
insert into #t values (1,'a')
insert into #t values (1,'b')
insert into #t values (2,'c')
insert into #t values (3,'a')
insert into #t values (3,'b')
insert into #t values (3,'c')
create table #t1 (id int , names varchar(100))
declare @a varchar(100) ,@i int =1
while (@i < = (select MAX(id ) from #t))
begin
set @a = ''
select @a = Coalesce(@a+',','')+name from #t where id = @i
insert into #t1 values (@i,substring(@a,2,LEN(@a)))
set @i = @i +1
end

Thanks

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

squvi.87 (6/2/2016)
Hi there,
Following code will do the job for me. But I wonder is there any other way to accomplish.
create table #t1 (id int , names varchar(100))
declare @a varchar(100) ,@i int =1
while (@i < = (select MAX(id ) from #t))
begin
set @a = ''
select @a = Coalesce(@a+',','')+name from #t where id = @i
insert into #t1 values (@i,substring(@a,2,LEN(@a)))
set @i = @i +1
end

Thanks

You forgot the ddl for #t.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

squvi.87 Default port Points: 1450 More actions · Answer 2

squvi.87

Default port

Points: 1450

June 2, 2016 at 5:21 am

#1882779

You forgot the ddl for #t.

added now

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There are several ways to do this, most of them are compared here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

ChrisM@Work (6/2/2016)
There are several ways to do this, most of them are compared here.

I would suggest that you use the XML PATH option (unless you already use CLR). If you want an even easier way to write it, check the following: http://www.sqlservercentral.com/articles/SSMS/138994/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Luis Cazares (6/2/2016)
ChrisM@Work (6/2/2016)
There are several ways to do this, most of them are compared here.
I would suggest that you use the XML PATH option (unless you already use CLR). If you want an even easier way to write it, check the following: http://www.sqlservercentral.com/articles/SSMS/138994/

That's pretty much the same conclusion reached by Mr Bertrand, Luis - and it gets my vote too 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden