Dynamic PIVOT SQL Server -表单/输入示例

-- 创建示例表
CREATE TABLE Sales (
    ProductID INT,
    ProductName VARCHAR(50),
    Year INT,
    Quarter INT,
    SalesAmount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO Sales (ProductID, ProductName, Year, Quarter, SalesAmount)
VALUES
    (1, 'Product A', 2021, 1, 1000),
    (1, 'Product A', 2021, 2, 1500),
    (1, 'Product A', 2021, 3, 2000),
    (1, 'Product A', 2021, 4, 1800),
    (2, 'Product B', 2021, 1, 1200),
    (2, 'Product B', 2021, 2, 1800),
    (2, 'Product B', 2021, 3, 2200),
    (2, 'Product B', 2021, 4, 1900);

-- 使用动态PIVOT进行转换
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 获取动态列名
SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(Quarter)
FROM (SELECT DISTINCT Quarter FROM Sales) AS Quarters;

-- 构建动态SQL语句
SET @sql = N'
SELECT ProductID, ProductName, Year, ' + @columns + '
FROM (
    SELECT ProductID, ProductName, Year, Quarter, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Quarter IN (' + @columns + ')
) AS PivotTable;';

-- 执行动态SQL语句
EXEC sp_executesql @sql;