Combine Columns to Row (Two columns) –XML Path



--DDLs for sample table and data
Create table Employee(ID int,Department int,Name Varchar(50) )
Insert into Employee values (1,1,'j.Doe')

Create table Executive(ID int,Department int,Name Varchar(50) )
Insert into Executive values (1,1,'Doe')
Insert into Executive values (2,1,'David')
 
Create table Supervisor(ID int,Department int,Name Varchar(50) )
Insert into Supervisor values (1,1,'Doe')
Insert into Supervisor values (2,1,'Jane') 

Create table Departments(ID int,DepartName Varchar(50) )
insert into Departments values(1,'Marketing')


;with mycte as (
Select em.ID,em.Name as EmName,d.DepartName, ex.Name as ExName,s.Name as SuperName 
FROM Employee em 
INNER JOIN Executive  ex on em.Department=ex.Department 
INNER JOIN Supervisor s On s.Department=em.Department 
INNER JOIN Departments d on em.Department=d.ID
)


SELECT Distinct ID, EmName,DepartName
,SupervisorNames = STUFF((
SELECT distinct ',' + SuperName
 FROM mycte m
 WHERE m.ID = m1.ID AND m.EmName = m1.EmName AND m.DepartName = m1.DepartName

 FOR XML PATH('')), 1, 1, '')


, ExecutiveNames = STUFF((
SELECT distinct ',' + ExName
 FROM mycte m
 WHERE m.ID = m1.ID AND m.EmName = m1.EmName AND m.DepartName = m1.DepartName

 FOR XML PATH('')), 1, 1, '') 
 FROM mycte m1


--Clean up
drop table Employee,Executive,Supervisor,Departments
 
 
 


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s