Concatenating Rows

Question

Concatenating Rows

Viewing 15 posts - 136 through 150 (of 159 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

arturv (3/4/2011)
Hi guys,
why dont we keep it simple?
CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)
INSERT INTO #test ( id, name )
VALUES ( 101, 'Apple' ),
( 102, 'Banana' ),
( 103, 'Orange' ),
( 104, 'Melon' ),
( 105, 'Grape' )
SELECT * FROM #test
DECLARE @fruit VARCHAR(MAX)
SELECT @fruit = COALESCE(@fruit + '', '') + name
FROM #test
SELECT Colors = @fruit
It take no efford at all.
Artur

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.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arturv Grasshopper Points: 21 More actions · Answer 2

Hi Jeff,

Thanks for your reply.

You're right... this can be used as an example of RBAR.

But if we consider that this specific operation is limited to 8000 characters, its a very "small" RBAr:-D

Its all comes down to what is pretended, how much it costs to develop, and the time you have, the available resources, etc...

Because there are many ways to achieve the same objective, we always show look for alternatives and choose the one that serves us better.

Sometimes the "best practices programming" its not best or more profitable solution.

But this is just an opinion

Artur

The answer to all questions about SQL Server is: It depends..., except "Should auto-shrink be enabled"

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

arturv (3/5/2011)
Sometimes the "best practices programming" its not best or more profitable solution.
But this is just an opinion

Then you won't mind me expressing my opinion. 😉 It takes no longer to do things correctly than it does to fall back on RBAR. The key is that you have to be well practiced enough to know the set based method instead of falling back on RBAR. The only way to do that is to practice.

Also, NEVER justify the use of RBAR just because of a supposedly small number of rows because you don't actually know what someone else will do with either the tables involved or the code. It'll also make you "weak" because, instead of practicing good set based methods, you keep using 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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

a.rajmane Old Hand Points: 377 More actions · Answer 4

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

a.rajmane Old Hand Points: 377 More actions · Answer 5

another simple way of doing this see at http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

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 [font="Arial Black"](SELECT DISTINCT SomeCharValue FROM dbo.SomeTable --Need to hardcode
WHERE SomeInt = @SomeInt) d[/font]
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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

guruprasat85 SSC Enthusiast Points: 136 More actions · Answer 14

Declare @var varchar(1000)

Set @var=''

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 15

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)