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.
Author |
Topic |
razeena
Yak Posting Veteran
54 Posts |
Posted - 2013-02-06 : 22:55:19
|
Hi, Please see two master table and a link table below.----------declare @tblClient table(cName varchar(30),cid int)insert into @tblClientselect 'anu',100 union all select 'binu',101 select * from @tblClientdeclare @tblPayor table(pName varchar(30),pid int)insert into @tblPayorselect 'Jacob',200 union all select 'Mary',201 union all select 'Lissy',202 select * from @tblPayordeclare @tblLink table(lid int,cid int,pid int)insert into @tblLink select 1,100,200 union all select 2,100,201 union all select 3,100,202 union all select 4,101,200select * from @tblLink---------------A cname can be assigned to multiple Pname.Based on the link table values, comma separated output requried like this.==========/*CName PNameAnu Jacob,Mary,LissyBinu Jacob*/===========Any ideas? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 23:02:40
|
[code]SELECT c.cName,STUFF((SELECT ',' + pName FROM @tblPayor p INNER JOIN @tblLink l ON l.pid = p.pid WHERE l.cid = c.cid ORDER BY l.lid FOR XML PATH('')),1,1,'') AS PNameFROM @tblClient coutput------------------------------------------------cName PName------------------------------------------------anu Jacob,Mary,Lissybinu Jacob[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|