Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenating Rows


Concatenating Rows

Author
Message
carl.anderson-1037280
carl.anderson-1037280
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 127
Comments posted to this topic are about the item Concatenating Rows
nick.mcdermaid
nick.mcdermaid
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 766
Perhaps something in your requirements precluded this method but how about this:

DECLARE @var varchar(8000)
SET @var = ''

SELECT @var = @var + FieldName + '/'
FROM Table

SELECT @Var
nicolay.tchernitsky
nicolay.tchernitsky
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
the XML solution can also be:

select replace(
convert(
varchar(max),
(select name+',' as [text()] from fruit order by id for xml path(''))
)+',,,'
,',,,,'
,'')

or, if there is no need in deleting the last comma and converting the result from xml to string, just

select name+',' as [text()] from fruit order by id for xml path('')
Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
To Modify:

DECLARE @var varchar(8000)

SELECT @var = ISNULL(@var + '/') + FieldName
FROM Table

SELECT @Var

However, this only works for one entity. For muliple entities the CTE recursion works fine, although it performs like crap. I only use this for throwaway SQL code generators where string handling in SQL makes sense.

For a scalable solution where it makes sense to do this in SQL, you need to turn the recursion into iteration. Most CTE recursion can be re-written to be iterative, allowing a set based solution.

Signature is NULL
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45108 Visits: 39915
Nicely written article but even the optimized solution causes a full cartesian product on the far right of the execution plan. That could explain it's performance problem.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45108 Visits: 39915
nicolay.tchernitsky (10/13/2009)
the XML solution can also be:

select replace(
convert(
varchar(max),
(select name+',' as [text()] from fruit order by id for xml path(''))
)+',,,'
,',,,,'
,'')

or, if there is no need in deleting the last comma and converting the result from xml to string, just

select name+',' as [text()] from fruit order by id for xml path('')


Flip it around and delete the first comma...

with   
fruit as (
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
)
SELECT STUFF((select ','+name from fruit order by id for xml path('')),1,1,'') AS CSV



--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jon Spink
Jon Spink
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 945
this is mostly useful when you have to group rows together by one field and concatenate the corresponding values for each group. How would the solution look then?
dystarry
dystarry
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 53
SELECT STUFF((
SELECT ', ' + name
FROM dbo.FRUIT
FOR XML PATH('')
), 1, 2, '');
a.everett
a.everett
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
so as newbie in the top post quotes why not just use a select @var+ to concatenate.
You said it thats fine for 1 entity granted can see that.
But just move the code to a function that accepts the entity pk key and returns the concat string.
Then just call the function from a select thats listing the entities you want reporting?

somthing like,

select entkey, getmylist(entkey) from myentlist
Jon Spink
Jon Spink
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 945
neat one dystarry. And here's the solution for rows grouped by type_id:

declare @type_id int
declare cursor1 CURSOR FAST_FORWARD FOR SELECT distinct type_id FROM fruit

drop table #Temp
create table #Temp (region_id int, atext varchar(100))

open cursor1
fetch next from cursor1 into @type_id
while @@fetch_status = 0
begin
print @type_id
insert into #Temp (region_id, atext)
select distinct @type_id,
STUFF((SELECT ', ' + name FROM dbo.fruit where type_id = @type_id FOR XML PATH('')), 1, 2, '')
from fruit
--group by region_id
fetch next from cursor1 into @type_id
end
close cursor1
deallocate cursor1

select * from #Temp
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search