串行化重复列【相同ID】

--Join the data with the same ID together
declare @tbl table (Sid int identity, ID int, Description varchar(100) )
insert into @tbl
select 1001, 'Test 1001'
union all
select 1001, 'Test 1001 dsdfsdf'
union all
select 1002, 'sdfsdf'
union all
select 1003, 'sdfsdddddddddd'
union all
select 1004, 'adc'
union all
select 1004, 'dddddd'
union all
select 1005, 'sdfeererer'

select distinct a.ID, Descs =
 (
  select Description+',' as [data()]
  from @tbl c
  where c.ID = a.ID
  order by c.ID
  for xml path('')
 )
from @tbl a
inner join (select ID from @tbl group by ID having count(ID) > 1) as b
on a.ID = b.ID
union
select distinct a.ID, Description
from @tbl a
where a.ID not in (select ID from @tbl group by ID having count(ID) > 1)

select distinct a.phy, phy_names = (
  select market+'%'+ltrim(rtrim(Phy_Name)) + '|' as [data()]
  from tblprovs c
  where c.phy = a.phy
  order by c.phy
  for xml path('')
 )
from tblprovs a
 inner join (select phy from tblProvs group by phy having count(phy) >=2) as b
on a.phy = b.phy

union

select a.phy,phy_name
from tblprovs a
inner join (select phy from tblprovs group by phy having count(phy)=1) as b
on a.phy = b.phy

order by phy