March 19, 2009 at 9:40 am
Hi All.
I am trying to concatenate multiple field values from a table in one string.
Example:
IF Select * from TABLE TEST gives following :
ColA ColB
1 John
1 Josh
1 Bob
2 Kathy
2 Jill
How would I render it to give below data:
ColA ColB
1 John, Josh, Bob
2 Kathy, Jill
Can someone give their inputs. Thanks for the help.
March 19, 2009 at 9:54 am
Here is some code you can work with on this.
declare @TestTab table (ColA int, ColB Varchar(10));
insert into @TestTab
select 1, 'John' union all
select 1, 'Josh' union all
select 1, 'Bob' union all
select 2, 'Kathy' union all
select 2, 'Jill'
;
select * from @TestTab;
select
ColA,
stuff((select ', ' + ColB from @TestTab t2 where t2.ColA = t1.ColA for xml path('')),1,2,'')
from
@TestTab t1
group by
ColA;
March 19, 2009 at 9:57 am
Thanks ! Lynn. Will give it a try.
March 19, 2009 at 11:16 am
Nice Job Lynn, tried to solve it myself, and I couldn't get to group them. I've seen you use the FOR XML a few times in other posts.
I got a question though, why do you use stuff at the end, wouldn't a substring function, or a right function be easier?
And oh, yeah.. when I use FOR XML on a simply column, it does not give the same output. i.e: (ColA)Test1(/ColA)(ColA)TEst2(/ColA) why does appending a string to it stop doing the ColA thing?
Thanks
Cheers,
J-F
March 19, 2009 at 11:38 am
I am going to have to defer your questions to someone who has a better knowledge of FOR XML PATH. I picked up this little trick here on SSC and it works very well, so when someone asks how to do it (which does happen a lot here on SSC), it's something easy to post back.
March 19, 2009 at 11:49 am
Thanks Lynn, hope a XML Guru can see this post! 😉
Cheers,
J-F
March 19, 2009 at 12:21 pm
Stuff gets rid of a preceding comma at the beginning of the string. That's all. Try the query without it, and you get:
, John, Josh, Bob
The Stuff command, as written here, puts a zero-length string in, the 1 tells it to put it at the beginning of the string, and the 2 tells it to replace 2 characters. Thus, it gets rid of the comma-space at the beginning. Can't use Replace, or you'd get rid of all of the commas, which would kind of defeat the purpose.
I guess I'm not clear on the other question about the XML. Using "path('')" is what gets rid of the column names, if that's what you're asking. Well, it replaces the column names with a zero-length string, to be precise.
- 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
March 19, 2009 at 12:27 pm
It's the FOR XML PATH part that I am not really sure about myself. That's why I was asking for some help here. I'd like to understand it better myself.
March 19, 2009 at 12:31 pm
Lynn Pettis (3/19/2009)
It's the FOR XML PATH part that I am not really sure about myself. That's why I was asking for some help here. I'd like to understand it better myself.
Sent out a "tweet" about it that Grant re-tweeted to expand the knowledge base.
Jack Corbett
Consultant - Straight Path Solutions
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
March 19, 2009 at 12:39 pm
Try this, see if it helps:
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Col1 char(1) not null);
insert into #T (Col1)
select substring('abcdefghijklmnopqrstuvwxyz', number, 1)
from dbo.Numbers
where number between 1 and 26;
select Col1
from #T
for XML path;
select Col1
from #T
for XML path('');
select ', ' + Col1
from #T
for XML path;
select ', ' + Col1
from #T
for XML path('');
You'll see that the comma gets rid of the Col1 header, and the ('') gets rid of the row header, so all you're left with is the value and the comma.
Try some variations on it. Put an "a" in there instead of a comma, or a vertical-pipe instead of a comma. Basically, anything that would result in "(no column name)" if you ran it as a regular query will accomplish what the comma does there.
- 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
March 19, 2009 at 1:05 pm
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!
March 19, 2009 at 1:05 pm
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
March 19, 2009 at 1:08 pm
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
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
March 19, 2009 at 1:15 pm
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 PATHWhich produces:
[font="Courier New"]<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>
[/font]
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:
[font="Courier New"]<ColB>John</ColB><ColB>Josh</ColB><ColB>Bob</ColB><ColB>Kathy</ColB><ColB>Jill</ColB>[/font]
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)
[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]
March 19, 2009 at 1:17 pm
Dang it! I hate how slow I am on posts... :angry:
[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]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply