Concatenation using FOR XML Path

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 180
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

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


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