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
 Site Related Forums
 Article Discussion
 Article: Converting Multiple Rows into a CSV String (Set Based Method)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/09/2002 :  20:53:17  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Australia
1591 Posts

Posted - 09/09/2002 :  21:32:56  Show Profile  Reply with Quote
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.."
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 09/10/2002 :  07:18:37  Show Profile  Reply with Quote
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}
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 09/10/2002 :  18:56:22  Show Profile  Reply with Quote
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.."
Go to Top of Page

IU84
Starting Member

3 Posts

Posted - 11/29/2003 :  00:56:30  Show Profile  Send IU84 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

urobertson
Starting Member

9 Posts

Posted - 12/11/2003 :  13:19:03  Show Profile  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 12/11/2003 :  13:26:22  Show Profile  Reply with Quote
I'm glad it worked well for you.

Jay White
{0}
Go to Top of Page

todhilton
Starting Member

USA
2 Posts

Posted - 05/12/2005 :  11:17:08  Show Profile  Visit todhilton's Homepage  Reply with Quote
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

Edited by - todhilton on 05/12/2005 11:20:23
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/12/2005 :  11:34:28  Show Profile  Reply with Quote
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
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/16/2006 :  13:37:15  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 11/19/2006 :  00:18:14  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000