Article: Converting Multiple Rows into a CSV String (Set Based Method)

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

Site Related Forums

Article Discussion

Article: Converting Multiple Rows into a CSV String (Set Based Method)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-09 : 20:53:17

Based the problem outlined in RocketScientist's article (entitled Converting Multiple Rows into a CSV String), I would like to present an alternative method for accomplishing a similar task. This article will show a faster method to convert multiple rows into multiple CSV strings.

Article Link.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-09 : 21:32:56

Some nice work there Page47..

This solution will work well with SQL 7 but with SQL 2K and the introduction of functions this technique, IMHO is redundant.

Because these row-column compression or concatenation routines are usually unique to a single table and cannot be made "generic" without dynamic SQL, the writing of function can significantly reduce IO..

Given your example data set....

Function


create function fnPage47
(@i int)
returns varchar(8000)
as
begin
declare @String varchar(8000)
Select @String = vc + ', ' + @String from Page47 where i = @I
return left(@String, len(@String)-1)
end
go

And the final query


set concat_null_yields_null off
Select i, dbo.fnPage47(i) as NewText from Page47
group by i

DavidM

"SQL-3 is an abomination.."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-10 : 07:18:37

True enough.

byrmol's funcion will outperform both RocketScientist's cursor based proc and my set based approach in a SQL 2K environment. This seems to hold true regardless of the composition of the dataset.

Although, I will contest the claim that the function is more "generic" as you would have write a function specific to each table just as you would with the approach presented. Maybe I am misunderstanding ....

Jay White
{0}

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-10 : 18:56:22

Jay,

I am sorry that came across that way..

My point about "generic" is this.. Because any technique to do this will never be generic, why not make it very specific and make a function for this purpose only.

English was never my strong point.
I am Australian after all...

DavidM

"SQL-3 is an abomination.."

IU84
Starting Member

3 Posts

Posted - 2003-11-29 : 00:56:30

Wow. That's an impressive piece of work. However, I like the implementation in Sybase SQLAnywhere much better...

Select
i, List( distinct vc )
from
Page47
group by i ;

Lookee there. An aggregate function on strings. And SQLAnywhere has had this since, oh, 1995?

IU84

urobertson
Starting Member

9 Posts

Posted - 2003-12-11 : 13:19:03

Page47,
Thank you so much for posting your article. I was using code similar to RocketScientist's and my procedure took 25 minutes to process 600 rows out of 42,000 (I stopped it manually after the 25 minutes since I was only testing and I'm not the only using the server. It was using way too many resources).

Don't even ask how long it took when I originally implemented it using a cursor.

I've implemented your code and now it takes 7 seconds. My boss is very happy with both of us :)

Thanks again,
Ursula

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-12-11 : 13:26:22

I'm glad it worked well for you.

Jay White
{0}

todhilton
Starting Member

2 Posts

Posted - 2005-05-12 : 11:17:08

I'm cross-posting this reply with this topic because my original searches resulted in both posts.

I realize this post is more than a year old, but I just recently had to do this and ended up using the temporary table method posted by Page47. It worked, but was clunky. A few days ago a co-worker showed me a really quick and easy way of doing this in SQL 2000:

declare @list varchar(8000)
select @list = Isnull(@list,'') +';'+ theColumnToBeConcatenated
from yourtable
where yoursearchparameter='something'
select @list

Yes, you can also wrap this into a function as byrmol suggests.

HTH!

~tod

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-12 : 11:34:28

quote:
Originally posted by todhilton

I'm cross-posting this reply with this topic because my original searches resulted in both posts.

I realize this post is more than a year old, but I just recently had to do this and ended up using the temporary table method posted by Page47. It worked, but was clunky. A few days ago a co-worker showed me a really quick and easy way of doing this in SQL 2000:

declare @list varchar(8000)
select @list = Isnull(@list,'') +';'+ theColumnToBeConcatenated
from yourtable
where yoursearchparameter='something'
select @list

Yes, you can also wrap this into a function as byrmol suggests.

Quite right, your co-workers method of creating a string is quite good; in fact, it is covered in a different article on this site.

If you study the article a bit closer, you will see that was is being accomplished here is quite different than the code you have provided.

Jay White

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-16 : 13:37:15

This is an old article ... written to SQL2K; however, I just wanted to update this to say a CLR User-Defined Aggregate Function is way faster than any approach previously suggested. Just look up "Invoking CLR User-Defined Aggregate Functions" in 2K5 BOL and M$ was nice enough to give us the code ...

Jay White

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-19 : 00:18:14

indeed. sql is pretty poor at complex string manipulation. compiled code will beat it every time in that arena. :)

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources