串行化重复列【相同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