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]