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 «««1234»»

Concatenating field values in one string Expand / Collapse
Author
Message
Posted Thursday, March 19, 2009 2:38 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
Lynn Pettis (3/19/2009)
Tell me, please, did it hurt?

Well, it worked. It wasn't terribly quick though. And nor was it dynamic. I had to do a count of the maximum number of lines, then add a couple "just in case", and then a whole string of isnull([#], '')s...

It wasn't pretty.

The FOR XML though (which I have tested, and works well) is fast!

One word of warning: the FOR XML will also transform some control characters (such as a carriage return) into the form '& # x 0 D ;'. I actually don't mind that, a quick REPLACE() sorts it out.
Post #679928
Posted Thursday, March 19, 2009 2:55 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Yeah, the XML stuff definitely works best for strings of numbers. Add in text, and you run the risk of having to clean it up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #679954
Posted Thursday, March 19, 2009 3:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Bruce W Cassidy (3/19/2009)
One word of warning: the FOR XML will also transform some control characters (such as a carriage return) into the form '& # x 0 D ;'.[/font]

That's know as the "Entitization problem" and there are several ways to address it. Using REPLACE alone is tough because there are a lot of different potential replacements.

The one that I use is to change this:
select
ColA,
stuff((select ', ' + ColB
from @TestTab t2
where t2.ColA = t1.ColA
for xml path('')),1,2,'')
from
@TestTab t1
group by
ColA;

into this:
select
ColA,
stuff( (select ', ' + ColB
from @TestTab t2
where t2.ColA = t1.ColA
for xml path(''), TYPE).value('.[1]', 'varchar(MAX)') ,1,2,'')
from
@TestTab t1
group by
ColA;

This fixes it, but adds some CPU overhead as well.


(edit: forgot the comma between PATH and TYPE...)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #679963
Posted Thursday, March 19, 2009 3:36 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
Nice! Although you're missing a comma after the path('')
Post #679988
Posted Thursday, March 19, 2009 3:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Oops! Right you are...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #679999
Posted Thursday, March 19, 2009 4:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
Thanks, Barry.

Like Lynn, I'd used this trick but not really understood all the details of how it worked. Your explanation is very clear.


Derek
Post #680020
Posted Friday, March 20, 2009 8:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Nice enhancement there! Definitely going in my goodies library.

(Actually, before it came up in this thread, I hadn't ever seen this solution at all before. Had to figure it out from scratch to see what it was doing. It's a pretty slick trick.)


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #680441
Posted Friday, March 20, 2009 9:04 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Thanks, Gus. I came up with that fix on my own. Adam Machanic, Ward Pond and some others have a different solution that they use (don't have it handy right now). It's more complicated, IMHO, but it might be faster (never compared them).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #680462
Posted Friday, January 17, 2014 10:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 11, 2014 2:56 PM
Points: 246, Visits: 83
Concatenating Unique Field Values

Lynn's solution does a great job of concatenating field values into one string. However, it repeats duplicates, which can be quite annoying with many duplicates.

If we change the code to create the table and populate to:

declare @TestTab table (ColA int, ColB Varchar(10));

insert into @TestTab
select 1, 'John' union all
select 1, 'Bob' union all
select 1, 'Josh' union all
select 1, 'Bob' union all
select 1, 'Bob' union all
select 1, 'Bob' union all
select 2, 'Kathy' union all
select 2, 'Jill'
;


Then we get the results:

1 John, Bob, Josh, Bob, Bob, Bob
2 Kathy, Jill


If we change her solution to add the "distinct" keyword, we only get unique values.


select * from @TestTab;

select
ColA,
stuff((select distinct ', ' + ColB from @TestTab t2 where t2.ColA = t1.ColA for xml path('')),1,2,'')
from
@TestTab t1
group by
ColA;


Then we get the unique results:

1 Bob, John, Josh
2 Jill, Kathy


Thanks Lynn for a marvelous piece of code that I will use many times in the future.

Bruce
Post #1532178
Posted Friday, January 17, 2014 11:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 3,342, Visits: 7,225
Wow, someone revived a 5 year old thread.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1532197
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse