create table #Tmp (nu int, val varchar(100))
insert into #Tmp values(1, 'a')
insert into #Tmp values(2, 'b')
insert into #Tmp values(2, 'c')
insert into #Tmp values(3, 'd')
insert into #Tmp values(3, 'e')
insert into #Tmp values(3, 'f')
select * from #Tmp
;with Orign as
(
select ROW_NUMBER() over(partition by nu order by nu) as rn, nu, val from #Tmp
),
recur as
(
select * from Orign where rn = 1
union all
select a.rn, a.nu, cast(b.val + ',' + a.val as varchar(100))as val
from Orign a
inner join recur b
on a.nu = b.nu and a.rn = b.rn+1
)
select nu, val
from recur a
where a.rn = (select max(rn)from recur b where a.nu = b.nu)
order by nu
drop table #Tmp
方法二：
create table #Tmp (nu int, val varchar(100))
insert into #Tmp values(1, 'a')
insert into #Tmp values(2, 'b')
insert into #Tmp values(2, 'c')
insert into #Tmp values(3, 'd')
insert into #Tmp values(3, 'e')
insert into #Tmp values(3, 'f')
insert into #Tmp values(4, 'g')
insert into #Tmp values(4, 'h')
insert into #Tmp values(4, 'i')
select * from #Tmp
select ','+val as [text()] from #Tmp t2 for xml path ('')
select nu, stuff( (select ','+val as [text()] from #Tmp t2 where t1.nu= t2.nu for xml path ('')),1,1,'')
from #Tmp t1
group by nu
drop table #Tmp 阅读全文
类别：Sql Server 查看评论 posted on 2009-05-24 13:44 Alan Gan 阅读(...) 评论(...) 编辑 收藏
转载于:https://www.cnblogs.com/AlanGan/archive/2009/05/24/1905779.html