January 31, 2011 at 12:57 pm
I know it's possible to collect phone numbers in a comma separated string using XmlPath('') and Stuff.
But I don't know how to do this.
This is what I have: SqlServer 2005 and 2 tables:
Names, e.g.
[font="Courier New"]NameID Name
1 John
2 Peter
3 Carin
4 Bill[/font]
and Phones, e.g.
[font="Courier New"]PhoneID NameID Phone
1 1 06-1234567
2 1 038-223344
3 1 033-344556
4 2 06-2211009
5 4 06-7654321
64 06-1122334[/font]
etc.
This is what I want as the result:
[font="Courier New"]NameID Name Phone
1 John 06-1234567, 038-223344, 033-344556
2 Peter 06-2211009
3 Carin
4 Bill 06-7654321, 06-1122334[/font]
Please, could you help me, I tried but couldn't find a solution
Here is a script to populate test data.
USE Test
CREATE TABLE [Names](
[NameID] int NOT NULL PRIMARY KEY,
[Name] varchar(30) NOT NULL)
CREATE TABLE [Phones](
[PhoneID] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
[NameID] int NOT NULL,
[PhoneNo] varchar(15) NOT NULL)
INSERT INTO [Names]([NameID], [Name]) VALUES(1, 'John')
INSERT INTO [Names]([NameID], [Name]) VALUES(2, 'Peter')
INSERT INTO [Names]([NameID], [Name]) VALUES(3, 'Carin')
INSERT INTO [Names]([NameID], [Name]) VALUES(4, 'Bill')
INSERT INTO [Phones]([NameID],[PhoneNo]) VALUES(1, '06-1234567')
INSERT INTO [Phones]([NameID],[PhoneNo]) VALUES(1, '038-223344')
INSERT INTO [Phones]([NameID],[PhoneNo]) VALUES(1, '033-344556')
INSERT INTO [Phones]([NameID],[PhoneNo]) VALUES(2, '06-2211009')
INSERT INTO [Phones]([NameID],[PhoneNo]) VALUES(4, '06-7654321')
INSERT INTO [Phones]([NameID],[PhoneNo]) VALUES(4, '06-1122334')
January 31, 2011 at 1:04 pm
I believe this article is what you were looking for:
http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/
Search only articles here, and use the search string: For XML delimited string
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 1:06 pm
Select from the parent table, and use an inline sub-query to get the phone numbers, using the For XML trick in that.
select *, (select phone from MyPhones where Person = MyPeople.Person for XML path('')) as phones
from MyPeople;
- 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
January 31, 2011 at 1:57 pm
@Craig, thank you for the article, I'am a noob on XML, so it gives me the background to understand what I'am doing.
@GSquared, I tryed your Sql-code, and it gives me almost what I want :cool::
[font="Courier New"]select *,
(select PhoneNo from Phones
where NameID = Names.NameID FOR XML PATH('')) as Phones
from Names;[/font]
But the result still has the XML tags in it e.g.:
<phoneNo>06-1234567</phoneNo><phoneNo>038-223344</phoneNo><phoneNo>033-344556</phoneNo>
What I need is another trick to remove the <phoneNo></phoneNo> parts and raplace it by a comma.
I once saw the trick, somewhere here on SqlServer.com, but I can't find it back :ermm:
January 31, 2011 at 2:13 pm
The trick is in the article Graig mentioned. I needed some time to find it.
The [font="Courier New"]as 'data()'[/font] addition gives the result without the <...>-parts:
[font="Courier New"]select *,
(select PhoneNo+ ',' as 'data()'
from Phones
where NameID = Names.NameID FOR XML PATH('')) as Phones
from Names;[/font]
Result:
[font="Courier New"]NameID Name Phone
1 John 06-1234567, 038-223344, 033-344556,
2 Peter 06-2211009,
3 Carin
4 Bill 06-7654321, 06-1122334,[/font]
Next: finding out the strange REPLACE approach to remove the last comma 😉
January 31, 2011 at 2:17 pm
Henk Schreij (1/31/2011)
The trick is in the article Graig mentioned. I needed some time to find it.
Odd, that link should have taken you right to it. Ah well.
Next: finding out the strange REPLACE approach to remove the last comma 😉
Actually, it's easier to do invert the problem.
Use ',' + phone_number
Then wrap it in a STUFF command IE: STUFF( (SELECT ....), 1, 1, '')
What that'll do is remove the first character. Much easier.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 3:02 pm
I tried the comma-before + stuff approach.
That's what I remembered, but couldn't reproduce.
But, it has a problem: there is a space before the comma.
The result looks like
[font="Courier New"]NameID Name Phone
1 John ,06-1234567 ,038-223344 ,033-344556
2 Peter ,06-2211009
3 Carin
4 Bill ,06-7654321 ,06-1122334[/font]
and after using stuff:
[font="Courier New"]NameID Name Phone
1 John 06-1234567 ,038-223344 ,033-344556
2 Peter 06-2211009
3 Carin
4 Bill 06-7654321 ,06-1122334[/font]
The space is now before the comma, and I like the space after the comma.
So I think I have to use the REPLACE approach.:hehe:
But, thanks a lot for your help 😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply