Help me a problem with T-SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help me a problem with T-SQL 2005

Author

Topic

txk2601
Starting Member

19 Posts

Posted - 2009-10-11 : 06:08:39

Hi all,

I have two table with following structure

Create table Person(
Id int primary key,
FullName nvarchar(50) not null,
)

Create table Phone(
Id int foreign key references Person(Id),
PhoneNumber nvarchar(40)
)

insert into Person values(1,'Nguyen Van A')
insert into Phone values(1,'0462733178')
insert into Phone values(1,'01238447729')
...

Now, i want get result by following format

Nguyen Van A | 0462733178 | 01238447729 ...

Please help me, thanks!

nalnait
Starting Member

14 Posts

Posted - 2009-10-11 : 07:28:07

HI,txk2601

Trying it:
SELECT * FROM Person p LEFT JOIN (
SELECT DISTINCT * FROM (SELECT DISTINCT id FROM Phone)a
OUTER APPLY(SELECT X=STUFF(REPLACE(REPLACE(
(SELECT DISTINCT PhoneNumber FROM Phone N WHERE id = a.id and isnull(PhoneNumber,'')<>'' FOR XML AUTO),
'<N PhoneNumber="', '|'), '"/>', ''), 1, 1, ''))N ) b ON p.id=b.id

OR
USE function do it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-11 : 07:49:42

check out PIVOT operator in the Books Online.

If you don't have a limit of number of phone number per person, you will need to use Dynamic SQL with that

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-11 : 14:45:52

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources