不带聚合函数的SQL Server数据透视查询

CREATE TABLE Sales (
    SalesPerson NVARCHAR(50),
    Product NVARCHAR(50),
    Amount INT
);

INSERT INTO Sales (SalesPerson, Product, Amount) VALUES
('Alice', 'ProductA', 100),
('Alice', 'ProductB', 150),
('Bob', 'ProductA', 200),
('Bob', 'ProductC', 250);

SELECT
    SalesPerson,
    MAX(CASE WHEN Product = 'ProductA' THEN Amount ELSE NULL END) AS ProductA,
    MAX(CASE WHEN Product = 'ProductB' THEN Amount ELSE NULL END) AS ProductB,
    MAX(CASE WHEN Product = 'ProductC' THEN Amount ELSE NULL END) AS ProductC
FROM Sales
GROUP BY SalesPerson;

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 获取所有产品的列名
SELECT @cols = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM Sales) AS Products;

-- 构建动态 SQL 查询
SET @query = '
SELECT SalesPerson, ' + @cols + '
FROM (
    SELECT SalesPerson, Product, Amount
    FROM Sales
) AS SourceTable
PIVOT (
    MAX(Amount)
    FOR Product IN (' + @cols + ')
) AS PivotTable
ORDER BY SalesPerson';

-- 执行动态 SQL 查询
EXEC sp_executesql @query;