Concatenation using FOR XML Path

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 255
Let's say we have a table as under

Declare @t table([Name] varchar(20),[Status] varchar(50))
Insert into @t values
('Sumith Mahi','Pending Approval'),('Sumith Mahi','Waiting Email Verfication')
,('Debjango Jani','Pending Approved'),('Debjango Jani','Sent email'),('Debjango Jani','Other activity')


Name 	        Status  
Sumith Mahi Pending Approval
Sumith Mahi Waiting Email Verfication
Debjango Jani Pending Approved
Debjango Jani Sent email
Debjango Jani Other activity


The expected output should be

Result  
Debjango Jani : Pending Approved,Sent email,Other activity
Sumith Mahi : Pending Approval,Waiting Email Verfication


Query
------


SELECT 
Result = [Name] +
' : ' +
' ' +
Stuff((Select ',' + CAST([Status] as varchar(100))
from @t t2
where t1.[Name] = t2.[Name]
for XML Path('')),1,1,'')
FROM @t t1
GROUP BY t1.[Name]

Comments or Responses

Login to post response