| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
 Posted - 09/09/2002 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 09/10/2002 : 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
Australia
1591 Posts |
Posted - 09/10/2002 : 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 - 11/29/2003 : 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 - 12/11/2003 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 12/11/2003 : 13:26:22
|
I'm glad it worked well for you.
Jay White
{0} |
|
|
|
todhilton
Starting Member
USA
2 Posts |
Posted - 05/12/2005 : 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 |
Edited by - todhilton on 05/12/2005 11:20:23 |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 05/12/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/16/2006 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/19/2006 : 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 |
|
|
| |
Topic |
|
Article: Converting Multiple Rows into a CSV String (Set Based Method)
