SQL 多列合并成一列

方法一：

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 查看评论