SQL 动态行转列

DECLARE @cmdText    VARCHAR(8000);
SET @cmdText = 'SELECT CommandNo,LotNum,WaferID,ProberNO,TestTime,isnull(Version,'''') as Version,SyncDateTime,Lastupdatetime,Model,' + CHAR(20);

select @cmdText =
@cmdText + ' CASE wld11 WHEN ''' + wld11 + ''' THEN WLD1  END AS '''+'WLD1' +'('+ wld11+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld11,'') as WLD11 FROM #tmp ) as T
select @cmdText =
@cmdText + ' CASE wld22 WHEN ''' + wld22 + ''' THEN WLD2  END AS '''+'WLD2' +'('+ wld22+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld22,'') as WLD22 FROM #tmp ) as T
select @cmdText =
@cmdText + ' CASE wld33 WHEN ''' + wld33 + ''' THEN WLD3  END AS '''+'WLD3' +'('+ wld33+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld33,'') as WLD33 FROM #tmp ) as T
select @cmdText =
@cmdText + ' CASE wld44 WHEN ''' + wld44 + ''' THEN WLD4  END AS '''+'WLD4' +'('+ wld44+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld44,'') as WLD44 FROM #tmp ) as T
select @cmdText =
@cmdText + ' CASE wld55 WHEN ''' + wld55 + ''' THEN WLD5  END AS '''+'WLD5' +'('+ wld55+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld55,'') as WLD55 FROM #tmp ) as T
select @cmdText =
@cmdText + ' CASE wld66 WHEN ''' + wld66 + ''' THEN WLD6  END AS '''+'WLD6' +'('+ wld66+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld66,'') as WLD66 FROM #tmp ) as T
select @cmdText =
@cmdText + ' CASE wld77 WHEN ''' + wld77 + ''' THEN WLD7  END AS '''+'WLD7' +'('+ wld77+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(wld77,'') as WLD77 FROM #tmp ) as T

SELECT @cmdText =
@cmdText + ' CASE lop221 WHEN ''' + lop211 + ''' THEN lop21  END AS '''+'LOP21' +'('+ lop211+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(lop211,'21') as lop211 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE lop221 WHEN ''' + lop221 + ''' THEN lop22  END AS '''+'LOP22' +'('+ lop221+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(lop221,'22') as lop221 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE lop231 WHEN ''' + lop231 + ''' THEN lop23  END AS '''+'LOP23' +'('+ lop231+')'+ ''','+  CHAR(10)  FROM (SELECT DISTINCT isnull(lop231,'23') as lop231  FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE lop241 WHEN ''' + lop241 + ''' THEN lop24  END AS '''+'LOP24' +'('+ lop241+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(lop241,'24') as lop241  FROM #tmp ) as T

SELECT @cmdText =
@cmdText + ' CASE VF111 WHEN ''' + VF111 + ''' THEN VF11  END AS '''+'VF11' +'('+ VF111+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(VF111,'VF11') as VF111 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE VF121 WHEN ''' + VF121 + ''' THEN VF12  END AS '''+'VF12' +'('+ VF121+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(VF121,'VF12') as VF121 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE VF131 WHEN ''' + VF131 + ''' THEN VF13  END AS '''+'VF13' +'('+ VF131+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(VF131,'VF13') as VF131 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE VF141 WHEN ''' + VF141 + ''' THEN VF14  END AS '''+'VF14' +'('+ VF141+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(VF141,'VF14') as VF141 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE VF151 WHEN ''' + VF151 + ''' THEN VF15  END AS '''+'VF15' +'('+ VF151+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(VF151,'VF15') as VF151 FROM #tmp ) as T
SELECT @cmdText =
@cmdText + ' CASE VF161 WHEN ''' + VF161 + ''' THEN VF16  END AS '''+'VF16' +'('+ VF161+')'+ ''',' + CHAR(10)  FROM (SELECT DISTINCT isnull(VF161,'VF16') as VF161 FROM #tmp ) as T

SELECT @cmdText =
@cmdText + 'StdWld,StdLop2,StdVf1,StdWlp1,AvgWld,AvgLop2,AvgVf1,AvgVf4,AvgWlp1,Yield,XMLVersion'','

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)
SET @cmdText = @cmdText + ' FROM #tmp ';
PRINT @cmdText
EXECUTE (@cmdText);

转换前：

转换后：