590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
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 1017111,14 union ALL
select 1017111,57 union ALL
select 1017111,44 union all
select 1128011,41 union all
select 1128011,26 union all
select 1128011,100 union all
select 1128011,44 union all
select 1128011,7
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)
--测试数据
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)