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