SQL Server交叉表联接

CREATE TABLE Sales (
    ProductID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

SELECT 
    ProductID,
    SUM(CASE WHEN MONTH(SaleDate) = 1 THEN Amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(SaleDate) = 2 THEN Amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(SaleDate) = 3 THEN Amount ELSE 0 END) AS Mar
FROM 
    Sales
GROUP BY 
    ProductID;

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(MONTH(SaleDate))
FROM (SELECT DISTINCT MONTH(SaleDate) FROM Sales) AS Months;

SET @sql = '
SELECT 
    ProductID, ' + @columns + '
FROM 
    (SELECT ProductID, MONTH(SaleDate), Amount FROM Sales) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR MONTH(SaleDate) IN (' + @columns + ')
) AS PivotTable;';

EXEC sp_executesql @sql;