Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Write for us

  
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Carl P. Anderson » Concatenating Rows
Add to briefcase
160 posts, Page 1 of 16
12345»»»

Concatenating Rows Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
carl.anderson-1037280
Posted Tuesday, October 13, 2009 10:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:06 PM
Points: 10, Visits: 114
Comments posted to this topic are about the item Concatenating Rows
Post #802508
nick.mcdermaid
Posted Tuesday, October 13, 2009 10:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:18 PM
Points: 180, Visits: 610
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
Post #802510
nicolay.tchernitsky
Posted Tuesday, October 13, 2009 11:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 17, 2009 11:06 PM
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('')
Post #802521
Calvin Lawson
Posted Wednesday, October 14, 2009 1:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #802540
Jeff Moden
Posted Wednesday, October 14, 2009 1:56 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:09 PM
Points: 36,636, Visits: 33,395
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."

(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 #802555
Jeff Moden
Posted Wednesday, October 14, 2009 2:03 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:09 PM
Points: 36,636, Visits: 33,395
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."

(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 #802556
Jon Spink
Posted Wednesday, October 14, 2009 2:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
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?
Post #802561
dystarry
Posted Wednesday, October 14, 2009 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 8:41 PM
Points: 7, Visits: 51
SELECT STUFF((
SELECT ', ' + name
FROM dbo.FRUIT
FOR XML PATH('')
), 1, 2, '');
Post #802565
a.everett
Posted Wednesday, October 14, 2009 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 3:49 AM
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
Post #802567
Jon Spink
Posted Wednesday, October 14, 2009 2:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
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
Post #802573
« Prev Topic | Next Topic »

Add to briefcase
160 posts, Page 1 of 16
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.