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