Check this..
Create Table #Temp(No int, Name Nvarchar(40))
Insert into #Temp
Select 1,'a' Union all
Select 1,'b' Union all
Select 1,'c' Union all
Select 2,'d'
Select Distinct t.No,
STUFF((Select Distinct a.Name From #Temp a
Where a.No=t.No
for Xml Path(''),TYPE).value('.','VARCHAR(max)'), 1, 0, '') as CombinedList
From #Temp t
Drop Table #Temp
Output:
No CombinedList
1 abc
2 d
Using a Recursive Cte...
Create Table #Temp(No int, Name Nvarchar(40))
Insert into #Temp
Select 1,'a' Union all
Select 1,'b' Union all
Select 1,'c' Union all
Select 2,'d'
;With cte (No,Name,RankNo) as
(
Select No,Name,ROW_NUMBER() Over(Partition by No Order by Name) from #Temp
)
,recurs_cte (No,CombinedName,RankNo) AS
(
SELECT Distinct No,Cast(Name as Nvarchar(Max)),RankNo FROM cte WHERE RankNo=1
UNION ALL
SELECT c.No,Cast(r.CombinedName + c.Name as Nvarchar(Max)),c.RankNo FROM recurs_cte r
INNER JOIN cte c ON r.No=c.No
Where c.RankNo=r.RankNo+1
)
Select No,CombinedName From recurs_cte r
Where RankNo in (Select Max(RankNo) from recurs_cte where No=r.No) Order by No
Drop Table #Temp