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