SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenating field values in one string


Concatenating field values in one string

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94363 Visits: 38955
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!

Cool
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)
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57433 Visits: 9730
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44393 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34794 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34794 Visits: 9518
Dang it! I hate how slow I am on posts... Angry

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57433 Visits: 9730
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
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94363 Visits: 38955
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.

Cool
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)
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34794 Visits: 9518
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."
Bruce W Cassidy
Bruce W Cassidy
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3087 Visits: 1033
Thanks folks! Would you believe I was doing this sort of thing with a pivot? Blush
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94363 Visits: 38955
Bruce W Cassidy (3/19/2009)
Thanks folks! Would you believe I was doing this sort of thing with a pivot? Blush


Tell me, please, did it hurt? ;-)

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