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 (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225730 Visits: 40423
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 (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: General Forum Members
Points: 140199 Visits: 9731
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 Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104385 Visits: 15047
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
Consultant Straight Path Solutions
Dont 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 QuestionHow 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 Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80730 Visits: 9519
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 Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80730 Visits: 9519
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 (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: General Forum Members
Points: 140199 Visits: 9731
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 (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225730 Visits: 40423
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 Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80730 Visits: 9519
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
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7121 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 (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225730 Visits: 40423
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