Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516

Concatenating Rows Expand / Collapse
Author
Message
Posted Monday, March 7, 2011 7:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
Jeff Moden (3/5/2011)
arturv (3/4/2011)
Hi guys,

why dont we keep it simple?

SELECT @fruit = COALESCE(@fruit + '', '') + name

Because that's RBAR an solves only for one scalar return. Also, if the list is long, the code begins to slow down almost exponentially because it has to keep rebuilding the variable internally as well as growing the memory allocation for the variable which is also relatively expensive.

Well it's not really RBAR - it is a set-based solution of sorts, but you are absolutely right to point out that it is not at all suitable for a large number of elements, or an otherwise long result.

There are several other methods which are much better: the FOR XML PATH trick is the one most commonly employed. Yes, it may be a bit of a hack (it's an odd use of 'XML') and the contortions required to handle special characters like & can be annoying, and it won't work at all with some rare embedded characters, but even with all those caveats, it's still generally much to be preferred. Until Microsoft provide a well-performing built-in CONCAT function, it's one of the better options.

Another high-performance solution which has no character restrictions and a more intuitive syntax is based on SQLCLR. For very large strings, the RBAR .WRITE method is surprisingly worth considering. You can find test scripts and performance comparisons here:

http://florianreischl.blogspot.com/2010/01/concatenation-of-text-and-binary-data.html

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1074138
Posted Monday, March 7, 2011 8:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
SQLkiwi (3/7/2011)[hrWell it's not really RBAR - it is a set-based solution of sorts, but you are absolutely right to point out that it is not at all suitable for a large number of elements, or an otherwise long result.


At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow".

Another high-performance solution which has no character restrictions and a more intuitive syntax is based on SQLCLR. For very large strings, the RBAR .WRITE method is surprisingly worth considering. You can find test scripts and performance comparisons here:


This DOES seem like one of those places where SQLCLR would certainly outstripe T-SQL prestidigitaion. Have you done a comparison between the two>

Another question, please because I don't work with C#... when you speak of the .WRITE method, are you talking about T-SQL or the SQLCLR?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1074196
Posted Monday, March 7, 2011 8:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
Never mind on the .WRITE method. I just found the answer in Flo's article that you provided a link for. Thanks for the link.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1074206
Posted Monday, March 7, 2011 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
Jeff Moden (3/7/2011)
At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow".

I think I see what you mean, but we're talking about the "SELECT @fruit = COALESCE(@fruit + '', '') + name FROM table" method aren't we? There's no UDF or explicit loop there...or am I missing something? Quite possible, it is 4:40am!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1074213
Posted Monday, March 7, 2011 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:10 PM
Points: 7, Visits: 179
This works to simplify the 'for xml path ('')' option:

select substring((Select rtrim(ltrim(','+name)) from fruit order by id for xml path('')),2,1000) as FruitList

The rtrim/ltrim function apparently removes the unprintable xml tokens, and the substring removes the first comma. This works consistently, where used without the rtrim/ltrim is spotty, as the author suggests.
Post #1074376
Posted Monday, March 7, 2011 5:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
SQLkiwi (3/7/2011)
Jeff Moden (3/7/2011)
At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow".

I think I see what you mean, but we're talking about the "SELECT @fruit = COALESCE(@fruit + '', '') + name FROM table" method aren't we? There's no UDF or explicit loop there...or am I missing something? Quite possible, it is 4:40am!


True enough. If you only need to create a single text aggregate, you don't need a scalar UDF. It's still RBAR, though. Explicit loops aren't the only form of RBAR. There is such a thing as "Hidden RBAR". You remember the "Triangular Join" thing for sums that you and I had fun with? Even Ben-gan wrote about the O2 problem with using COALESCE (or ISNULL) to overwrite variables in such a fashion.

My problem with people who use this method even on short stuff is that some unlucky sucker who has a schedule to meet may "leverage" the code for something bigger and against a whole table instead of just one "grouping".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1074543
Posted Monday, March 7, 2011 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
jim Etheridge (3/7/2011)
This works to simplify the 'for xml path ('')' option: {snip]
The rtrim/ltrim function apparently removes the unprintable xml tokens, and the substring removes the first comma. This works consistently, where used without the rtrim/ltrim is spotty, as the author suggests.


Thanks, Jim. Cool tip. I'm going to have to try it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1074544
Posted Tuesday, March 8, 2011 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:12 PM
Points: 3, Visits: 60
How about something like this: (similar to solution 2)

DECLARE @listCol VARCHAR(2000)
SELECT @listCol = STUFF(( SELECT DISTINCT ', ' + name
FROM fruit
ORDER BY 1
FOR XML PATH('')
), 1, 2, ' ')

select @listcol
Post #1075084
Posted Thursday, July 28, 2011 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 8:35 AM
Points: 1, Visits: 0
Thanks for the great post. It almost completely solved an issue I had at work. I only needed the concatenation to work by group within the result set. I posted my code on my blog here: [url=http://geekswithblogs.net/TomPepe/archive/2011/07/27/using-cte-to-concatenate-rows-by-group.aspx][/url]
Post #1150215
Posted Monday, August 15, 2011 11:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 19, 2012 11:45 PM
Points: 1, Visits: 10
solution 3
select LEFT(concatenations,len(concatenations)-1) from
(select (select cast(name as varchar)+','from fruit order by id for xml path('')) as concatenations)a
Post #1160363
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse