欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL两列数据,行转列

程序员文章站 2022-06-03 10:49:41
SQL中只有两列数据(字段1,字段2),将其相同字段1的行转列 转换前: 转换后: 转自:https://bbs.csdn.net/topics/392320974 ......

sql中只有两列数据(字段1,字段2),将其相同字段1的行转列

转换前:

SQL两列数据,行转列

转换后:

SQL两列数据,行转列

 

--测试数据
if not object_id(n'tempdb..#t') is null
    drop table #t
go
create table #t([mdf_lot_no] int,[erp_mode_cd] int)
insert #t
select 1017111,5 union all
select 1017111,41 union all
select 1128011,41 union all
select 1128011,26
go
--测试数据结束
declare @name varchar(max),@sql varchar(max)
;with cte as (
select  * ,
        row_number() over ( partition by mdf_lot_no order by getdate() ) as rn
from    #t
)
select @name =stuff((select distinct ',['+rtrim(rn)+']'  from cte for xml path('')),1,1,'')
set @sql =';with cte as (
select  * ,
        row_number() over ( partition by mdf_lot_no order by getdate() ) as rn
from    #t
)'
set @sql =@sql+'select * from cte pivot(max([erp_mode_cd])for rn in('+@name+'))a'
print @sql
exec( @sql)

转自:https://bbs.csdn.net/topics/392320974