动态SQL解决PIVOT透视多列的问题

有时需要透视的列比较多，或不固定，用动态SQL可以很好地解决问题：

 

CREATE PROCEDURE [dbo].[usp_getErrWeightReportReceiver]ASBEGIN     DECLARE @PLANTS VARCHAR(500)=''DECLARE @SQL NVARCHAR(1000)    SELECT @PLANTS = @PLANTS + '[' + VALUE + '],'    FROM (SELECT DISTINCT VALUE FROM [dbo].[errWeightMail] WHERE ID='FSL_MAILTO' AND VALUE='QQ20') T    SET @PLANTS= LEFT(@PLANTS, LEN(@PLANTS)-1)     SET @SQL=    'WITH A AS(SELECT ID,VALUE,VALUE PLANT,VALUE3 FROM [dbo].[errWeightMail] WHERE ID=''FSL_MAILTO'' AND VALUE=''QQ20'')SELECT PLANT,{0} FROM A PIVOT(MIN(VALUE3) FOR VALUE IN({0}))T'    SET @SQL= REPLACE(@SQL,'{0}',@PLANTS)    EXEC sp_executesql @SQL END