SQL多列转一列实战技巧-优快云博客

这篇博客分享了如何在SQL中将多列数据合并为一列的实用技巧，适用于数据整理和分析场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方法一：

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