Rahul Rahul - 3 months ago 12
SQL Question

Merge single column of first record with respective to duplicate record in SQLServer

I have this data in sqlserver


ID Phone
1 100
1 200
2 300
2 300
3 400
3 500
3 600


I want output like this


ID Phone
1 100,200
1 200
2 300
2 300
3 400,500,600
3 500
3 600

Answer
;with cte
as
(select id,phone,
stuff((select  distinct','+cast(phone as varchar(20))
from #valuee t2 where t1.id=t2.id
for xml path('')),1,1,'') as nwphone
from #valuee t1
)
select id,
case when 1=dense_rank() over (partition by id,nwphone order by phone) 
then cast(nwphone as varchar(20)) else cast(phone as varchar(20)) end as phone
from
cte

Output:

id  phone
1   100,200
1   200
2   300
2   300
3   400,500,600
3   500
3   600