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 1:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
Okay, so the concatenation of ', ' + Col1 results in the loss of the column name and the FOR XML PATH ('') replaces the . I think I understand a little better what is going on with it now. Thanks!


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #679814
Posted Thursday, March 19, 2009 1:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
You're welcome.

- 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 #679816
Posted Thursday, March 19, 2009 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 10,381, Visits: 13,436
Hey, I think I almost understand it now. It's an xml doc without tags and since the XML is returned in one column you now have a delimited list.

Makes since when you think about it and see some results.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #679820
Posted Thursday, March 19, 2009 1:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Gus already explained the STUFF trick to take away the leading comma, so I will try to explain the XML PATH trick. Not because I am an XML Guru, but rather because I just recently spent some considerable time trying to understand it myself:

The SELECT...FOR XML PATH feature is intended to allow you to easily build XML. The basic application on our sample data (from Lynn's example) would be:
Select ColB
From @TestTab
For XML PATH

Which produces:
<row>
  <
ColB>John</ColB>
</
row>
<
row>
  <
ColB>Josh</ColB>
</
row>
<
row>
  <
ColB>Bob</ColB>
</
row>
<
row>
  <
ColB>Kathy</ColB>
</
row>
<
row>
  <
ColB>Jill</ColB>
</
row>

Now this does technically concatenate our strings together, however, you will notice two problems with it:

1) the {row} tags, and
2) the {ColB} tags

(the line breaks and indentations were added by me for easy reading).

The {row} tags are intended to reflect the source table's name, but they can be overridden by the PATH argument. Using PATH('TableVar') would give us {TableVar} tags instead. As it happens a specific feature of FOR XML PATH is that if you use an explicitly empty name: PATH(''), instead of giving us empty row tags ({}...{/}) which would be invalid XML, it removes the row tags altogether. So:
Select ColB
From @TestTab
For XML PATH('')

This gives us the following:
<ColB>John</ColB><ColB>Josh</ColB><ColB>Bob</ColB><ColB>Kathy</ColB><ColB>Jill</ColB>
So we still have the column tags to deal with.

Now it is clear the the column tags are coming from the column name, so you might think that all we have to do is give the column a blank name and the column tags will disappear also. And yes, that is true, but that is harder than it sounds because there is no way to explicitly give a column a blank name.

For instance, "SELECT ColB as [] From @TestTab" is invalid syntax. However, it turns out that there is an implicit way to do this: output columns that are expressions have no column name unless you explicitly give them one.

Thus, we can eliminate our column names by using some null operation on our column, like so:
Select ''+ ColB
From @TestTab
For XML PATH('')

which gives us:
JohnJoshBobKathyJill

Now all that's left is to add in the comma separator. In a happy coincidence, this merges perfectly with the previous trick, like so:
Select ','+ ColB
From @TestTab
For XML PATH('')

which finally gives us:
,John,Josh,Bob,Kathy,Jill


And this takes us back to the STUFF trick to get rid of the leading comma, as explained by Gus.

(edit: typos)


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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Dang it! I hate how slow I am on posts...

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
RBarryYoung (3/19/2009)
Dang it! I hate how slow I am on posts...


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
Post #679839
Posted Thursday, March 19, 2009 1:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
RBarryYoung (3/19/2009)
Dang it! I hate how slow I am on posts...


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

Thank you for taking the time add your comments.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #679840
Posted Thursday, March 19, 2009 1:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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.

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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
Thanks folks! Would you believe I was doing this sort of thing with a pivot?
Post #679865
Posted Thursday, March 19, 2009 1:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
Bruce W Cassidy (3/19/2009)
Thanks folks! Would you believe I was doing this sort of thing with a pivot?


Tell me, please, did it hurt?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #679874
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse