不同列和多列上的T-SQL动态透视

CREATE PROCEDURE dbo.DynamicPivotSales
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @Columns NVARCHAR(MAX) = N'';

    -- 动态生成列名
    SELECT @Columns = COALESCE(@Columns + ', ', '') + QUOTENAME(Region + '_' + CAST(SalesDate AS VARCHAR(10)))
    FROM (SELECT DISTINCT Region, SalesDate FROM Sales) AS UniqueValues;

    -- 构建动态SQL
    SET @SQL = N'
        SELECT ' + @Columns + '
        FROM (
            SELECT 
                Region,
                SalesDate,
                SalesAmount
            FROM Sales
        ) AS SourceTable
        PIVOT (
            SUM(SalesAmount)
            FOR Region + '_' + CAST(SalesDate AS VARCHAR(10)) IN (' + @Columns + ')
        ) AS PivotTable;
    ';

    -- 执行动态SQL
    EXEC sp_executesql @SQL;
END
GO

EXEC dbo.DynamicPivotSales;