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

转换前:

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

SQL两列数据,行转列 随笔 第1张

转换后:

SQL两列数据,行转列 随笔 第2张

 

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

扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄