如何通过分组实现行转列？能否用Pivot或Cross Apply完成？

原始数据表结构与初始化SQL #

CREATE TABLE SalesData (
    ID INT,
    ProductName VARCHAR(50),
    Year INT,
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO SalesData (ID, ProductName, Year, SalesAmount)
VALUES
(1, 'ProductA', 2021, 150.00),
(2, 'ProductB', 2022, 200.00),
(3, 'ProductA', 2022, 300.00),
(4, 'ProductB', 2021, 400.00);

期望转换结果 #

可以用Pivot实现吗？ #

完全可以。Pivot的核心是通过聚合实现行转列，我们需要先将ProductA和ProductB的年份、销售额做配对关联，再用聚合函数整理成目标列。以下是具体实现：

WITH ProductPairs AS (
    -- 为每个ProductA记录匹配年份不同的ProductB记录
    SELECT
        a.Year AS Product_Year,
        b.Year AS ProductB_Year,
        a.SalesAmount AS ProductA_SalesAmount,
        b.SalesAmount AS ProductB_SalesAmount
    FROM SalesData a
    JOIN SalesData b 
        ON a.ProductName = 'ProductA' 
        AND b.ProductName = 'ProductB' 
        AND a.Year != b.Year
)
SELECT
    ROW_NUMBER() OVER (ORDER BY Product_Year) AS ID,
    Product_Year,
    ProductB_Year,
    ProductA_SalesAmount,
    ProductB_SalesAmount
FROM ProductPairs;

如果要更贴合Pivot标准语法，也可以先拆分数据再聚合：

WITH PivotSource AS (
    SELECT
        -- 生成分组标识，确保ProductA和对应ProductB分到同一组
        CASE ProductName 
            WHEN 'ProductA' THEN Year 
            ELSE (SELECT Year FROM SalesData WHERE ProductName='ProductA' AND Year != s.Year) 
        END AS GroupID,
        ProductName,
        Year,
        SalesAmount
    FROM SalesData s
)
SELECT
    ROW_NUMBER() OVER (ORDER BY GroupID) AS ID,
    GroupID AS Product_Year,
    MAX(CASE WHEN ProductName='ProductB' THEN Year END) AS ProductB_Year,
    MAX(CASE WHEN ProductName='ProductA' THEN SalesAmount END) AS ProductA_SalesAmount,
    MAX(CASE WHEN ProductName='ProductB' THEN SalesAmount END) AS ProductB_SalesAmount
FROM PivotSource
GROUP BY GroupID;

Cross Apply的替代方案 #

Cross Apply可以灵活关联子查询，为每条主查询记录生成对应关联数据，非常适合这类配对转换需求，代码逻辑更直观：

SELECT
    ROW_NUMBER() OVER (ORDER BY a.Year) AS ID,
    a.Year AS Product_Year,
    b.Year AS ProductB_Year,
    a.SalesAmount AS ProductA_SalesAmount,
    b.SalesAmount AS ProductB_SalesAmount
FROM SalesData a
CROSS APPLY (
    -- 筛选出与当前ProductA年份不同的ProductB记录
    SELECT Year, SalesAmount
    FROM SalesData
    WHERE ProductName = 'ProductB' AND Year != a.Year
) b
WHERE a.ProductName = 'ProductA';

内容的提问来源于stack exchange，提问作者Dennis Xavier