SQL动态行转列

DECLARE @cols AS NVARCHAR(MAX)

SELECT @cols = COALESCE(@cols + ',[' + CAST(Month AS NVARCHAR(50)) + ']', '[' + CAST(Month AS VARCHAR(50)) + ']')

FROM ProductSales

GROUP BY Month

ORDER BY Month

DECLARE @query AS NVARCHAR(MAX)

SET @query = 'SELECT * FROM (SELECT ProductName, Year, Month, SUM(SalesAmount) AS TotalSales FROM ProductSales GROUP BY ProductName, Year, Month) AS T PIVOT (SUM(TotalSales) FOR Month IN (' + @cols + ')) AS P'

EXECUTE(@query)