sql_行转列

IF object_id('tempdb..#T') IS NOT NULL
  DROP TABLE #T

IF object_id('tempdb..##OMS_ColorSize201407231125') IS NOT NULL
  DROP TABLE ##OMS_ColorSize201407231125

--建一个要横排的列值的临时表
SELECT   ColorID
INTO     #T
FROM     SD_Web_Exhibition_StylesColor
GROUP BY ColorID

--将转列的值拼接成一个字符串
DECLARE @SQL NVARCHAR(MAX) = N'';
SET @SQL      = STUFF((SELECT N',' + QUOTENAME(ColorID)
                       FROM   #T
                       FOR XML PATH ( '' )),
                      1,
                      1,
                      N'');

--行转列sql: 会根据BeginQty转ColorID列,其他列自动group by
SET @SQL      =
                N'Select * into ##OMS_ColorSize201407231125 from SD_Web_Exhibition_StylesColor pivot(max(BeginQty) for ColorID in('
                + @SQL + N')) as p ';

--执行
EXEC(@SQL)

SELECT * FROM ##OMS_ColorSize201407231125

DROP TABLE ##OMS_ColorSize201407231125