Query for this reqt - SQL Server Forums

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)

Query for this reqt

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-13 : 05:00:16

Hi, plz provide the query for below reqt


The table is as below

Name  Dept1  Dept2
Syam   A,B     C,D
Syam   F       D,L
Syam   M,A     Y

I am looking for the output as below

Name  Result
Syam  A, B, F, M, A, C, D, D, L, Y

I tried below query. But this is only for one column. how can i appedn second column data to this.

Select top 1 Name,
stuff((select ','+Dept1 from table where Name = 'Syam' for xml path('')),1,1,'') 
as Result
from Table t where Name = 'Syam'

From above query i got result as 

Syam   A, B, F, M, A

But i want output as

Syam A, B, F, M, A, C, D, D, L, Y

developer :)

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-13 : 05:14:28

create Table #T(Name varchar(100), Dept1 varchar(100), Dept2 varchar(100))

Insert into #T
Select 'Syam', 'A,B','C,D' union all
Select 'Syam','F','D,L'union all
Select 'Syam','M,A','Y'

Select * from #T

SELECT
Name,
LEFT(T.Dept1,LEN(T.Dept1)-1) AS Dept1
FROM (
SELECT DISTINCT Name
FROM #T
) T1
CROSS APPLY
(
SELECT Dept1 + ','+ Dept2 + ','
FROM #T T2
WHERE T1.Name=T2.Name
FOR XML PATH ('')
) T(Dept1)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-10-13 : 07:06:30

try like this

Select top 1 Name,
stuff((select ','+Dept1 + +','+ Dept2 from #T for xml path('')),1,1,'') as Result
from #T t where Name = 'Syam'

Select top 1 Name,
stuff((select ','+Dept1 from #T for xml path('')) + (select ','+ dept2 from #t for xml path('')),1,1,'') as Result
from #T t where Name = 'Syam'

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-13 : 08:52:07

Thank you bklr.

quote:
Originally posted by bklr

try like this

Select top 1 Name,
stuff((select ','+Dept1 + +','+ Dept2 from #T for xml path('')),1,1,'') as Result
from #T t where Name = 'Syam'

Select top 1 Name,
stuff((select ','+Dept1 from #T for xml path('')) + (select ','+ dept2 from #t for xml path('')),1,1,'') as Result
from #T t where Name = 'Syam'

developer :)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-10-13 : 09:40:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources