22,209
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
CREATE TABLE #temp(id INT, A INT, B INT, C INT , D INT, E INT ,F INT, G INT)
insert #temp
select '123',1,1,1,1,NULL, NULL,NULL
DECLARE @fieldlist NVARCHAR(200), @sql NVARCHAR(MAX)
SET @fieldlist = STUFF(
(
SELECT (','+QUOTENAME(B.工序名称))
FROM #TEMP A
UNPIVOT
(值 FOR 工序名称 IN([A],[B],[C],[D],[E],[F],[G])) B --这儿楼主自己补齐了
FOR XML PATH('')
),1,1,''
)
SET @sql = N'
SELECT *
FROM #TEMP A
UNPIVOT
(值 FOR 工序名称 IN([A],[B],[C],[D],[E],[F],[G])) B --这儿楼主自己补齐了
PIVOT
(MAX(值) FOR 工序名称 IN('+ @fieldlist +')) C
'
--PRINT @sql
EXEC (@SQL)
/*
id A B C D
123 1 1 1 1
*/
WITH a1 (ID,A,B,C,D,E,F,G) AS
(
SELECT 123,1,1,1,1,NULL,NULL,NULL
)
,a2 AS
(
SELECT ID,'A' NAME,A VALUE FROM a1
UNION ALL
SELECT ID,'B',B FROM a1
UNION ALL
SELECT ID,'C',C FROM a1
UNION ALL
SELECT ID,'D',D FROM a1
UNION ALL
SELECT ID,'E',E FROM a1
UNION ALL
SELECT ID,'F',F FROM a1
UNION ALL
SELECT ID,'G',G FROM a1
)
SELECT * INTO #cu1 FROM a2 NAME WHERE VALUE IS NOT NULL
select distinct NAME into #cu2 from #cu1
declare @sql varchar(max),@sql2 varchar(max)
select @sql = isnull(@sql + '],[' , '') + NAME from #cu2
set @sql = '[' + @sql + ']'
select @sql2 = isnull(@sql2 + ',' , ',') + 'isnull(['+NAME+'],0) ['+NAME+']' from #cu2
set @sql='select ID'+@sql2+' from #cu1 a pivot (MAX(VALUE) for NAME in (' + @sql + ')) b order by ID'
exec (@sql)