SQL两列数据,行转列

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

转换前:

转换后:

 

--测试数据
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