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 Sunday, March 06, 2011 11: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 @ 5:03 PM
Points: 35,954, Visits: 30,243
a.rajmane (3/6/2011)
Another simple way of doing this ... see the example at http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx


You had a bad link in your post. I fixed it in the above quote.

Both of those methods have already been covered on this thread. The first method is the RBAR method I've been suggesting that people don't use.



--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073875
Posted Sunday, March 06, 2011 11:36 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
vkhabas (3/4/2011)
Sorry Guys, as I remember the CTE recursion works with depth <= 70


Actually, the default for the number of resursions is 100. The max explicit size is a bit over 32,000. However, if you set the MAXRECURSION option to 0, it can run virtually forever.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073876
Posted Sunday, March 06, 2011 11:39 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
RHaverty 8478 (3/4/2011)
My solution where I posted the coalesce solution will eat up a cursor on performance. Another better solution than using a cursor is:


But, it's not. It still contains a WHILE loop which gives no better performance than a read only, forward only, static cursor. It's not the cursor that slows things down. It's the WHILE loop needed to step through the process.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073877
Posted Sunday, March 06, 2011 11:42 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
kaplan.dani (3/4/2011)
I think this (using a cursor to iterate on the list) is the most reasonable solution for the newbie sql dveloper.
Is there a performance issue in this way ?
I think it shuold beat the recursion any time, and I'm not sure about the other one (xml path)

Any insights ?



Yes... the XML Path method of concatenating will blow the doors off of Cursors, While Loops, AND Recursive CTE's. My other thought is that Newbie SQL Developers should not be allowed to use any of those looping methods for the first two years of their career. Instead, they should be taught proper set-based methods instead of learning how to use the crutch of RBAR.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073878
Posted Sunday, March 06, 2011 11:47 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
burtsev (3/4/2011)
bnordberg (10/14/2009)
I recently had to concatenate ~1.5 billion rows of text (average length of 110 characters). These were medical notes where each line was a sepearte row. I tested CLR, Stored procedures ... nothing was a fast as the XML method. However I ran into tons of unicode characters that I had to replace. So I had a yucky 18 or so level nested replace. I was able to get the concatenation to work in 2.4 hours creating 34 million documents out of the 1.5 billion. The CLR would have definitly been useful to avoid the unicode character problem, but still I can't get it to beat the xml method.


Hi, have you tried to extract strings from XML correctly as it described here?
http://www.codeproject.com/Tips/122630/String-concatenation-in-Transact-SQL.aspx


That's not actually a "correct" method for XML because of two things. The first thing is the output is directed to a scalar variable. The second thing is that if you really want to get rid of the "unicode character problem", use the keyword TYPE.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073880
Posted Sunday, March 06, 2011 11:49 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
Carlo Romagnano (10/17/2009)
msam77-503326 (10/15/2009)
Below is simple sql that does that

declare @res varchar(Max)

select @res = coalesce(@res + ',', '') + [name]
from
(
select 'Apple' as name, 101 as id union all
select 'Banana' as name, 102 as id union all
select 'Orange' as name, 103 as id union all
select 'Melon' as name, 104 as id union all
select 'Grape' as name, 105 as id
) a

select @res


The best one always is my version without coalesce, replace and without removing final comma:

create table fruit(name varchar(20))
insert into fruit SELECT 'apple'
union all select 'banana'
union all select 'orange'
union all select 'melon'
union all select 'grape'

declare
@l varchar(8000)
,@comma varchar(2)
set @l = ''
set @comma = ''
select @l = @l + @comma + name
,@comma = ', '
from fruit
ORDER BY name
select @l


Try your RBAR method with a million rows of data. You'll change your mind about it being the "best".


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073882
Posted Sunday, March 06, 2011 11:51 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
Jeff Moden (10/18/2009)
And here's a function for items that don't exceed 8K...

--===== Create a function that uses VARCHAR(8000) with a DUPE Eliminator
CREATE FUNCTION dbo.Concat8KTestDupElim
(@SomeInt INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ReturnString VARCHAR(8000)
SELECT @ReturnString = ISNULL(@ReturnString+',' ,'') + d.SomeCharValue
FROM (SELECT DISTINCT SomeCharValue FROM dbo.SomeTable --Need to hardcode
WHERE SomeInt = @SomeInt) d

RETURN @ReturnString
END
GO



BTW... just so folks know. Even this is RBAR because it's a scalar function. I apologize for even posting 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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073884
Posted Sunday, March 06, 2011 11:54 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
Robert Bourdeau (3/4/2011)
It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.


Hi Robert,

I know this is an SQL Server forum but I sure would like to see the Oracle code that uses CONNECT BY to do concatenation to build CSV's like what they have on this thread. Thanks.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1073885
Posted Monday, March 07, 2011 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 5:38 AM
Points: 6, Visits: 91
Declare @var varchar(1000)
Set @var=''

Select @var = @var + fruit + ',' from #temp

Select substring(@var,1,len(@var)-1)
Post #1074018
Posted Monday, March 07, 2011 6:14 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
guruprasat85 (3/7/2011)
Declare @var varchar(1000)
Set @var=''

Select @var = @var + fruit + ',' from #temp

Select substring(@var,1,len(@var)-1)


Please read the posts above for why that's a bad idea and what to do about 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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1074093
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»

Permissions Expand / Collapse