Concatenating field values in one string

Question

Concatenating field values in one string

Viewing 15 posts - 16 through 30 (of 30 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

RBarryYoung (3/19/2009)
Dang it! I hate how slow I am on posts... :angry:

Well, your post is better organized and more eloquent than mine, so that's helpful.

- 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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

RBarryYoung (3/19/2009)
Dang it! I hate how slow I am on posts... :angry:

Nothing to be upset about. Your explanation helps as well.

Thank you for taking the time add your comments.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Thanks guys. It is one of the strange ironies of my life that although I can write code faster than most people, I am excruciatingly slow (and error-prone) at writing English.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Thanks folks! Would you believe I was doing this sort of thing with a pivot? :blush:[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Bruce W Cassidy (3/19/2009)
[font="Verdana"]Thanks folks! Would you believe I was doing this sort of thing with a pivot? :blush:[/font]

Tell me, please, did it hurt? 😉

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

Lynn Pettis (3/19/2009)
Tell me, please, did it hurt? 😉

[font="Verdana"]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.

[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 7

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 9

[font="Verdana"]Nice! Although you're missing a comma after the path('') :-D[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Oops! Right you are...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

StarNamer SSCrazy Eights Points: 8744 More actions · Answer 11

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

GSquared SSC Guru Points: 260824 More actions · Answer 12

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

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

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bruce Dow Ten Centuries Points: 1019 More actions · Answer 14

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 2, 'Kathy' union all

select 2, 'Jill'

;

Then we get the results:

1John, Bob, Josh, Bob, Bob, Bob

2Kathy, 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:

1Bob, John, Josh

2Jill, Kathy

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

Bruce

Luis Cazares SSC Guru Points: 183698 More actions · Answer 15

Wow, someone revived a 5 year old thread. :w00t:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2