如何将Azure零售价格表中3行数据转置为3列？

我已经下载了Azure零售价格表，每个资源的常规单价（unitPrice）、1年预留价格、3年预留价格分别以3行数据存储，现在需要把reservationTerm为'1 Year'和'3 Years'的unitPrice转成单独的列，让每个资源的所有价格信息都在同一行。

之前尝试遇到的问题：

• 使用PIVOT语句时先触发Incorrect syntax near the keyword 'pivot'语法错误，调整后又因NULL值出现报错
• 尝试条件聚合时，结果里的价格分散在不同行，不符合预期

测试表结构与数据 #

先给出测试用的表创建和数据插入脚本：

CREATE TABLE AzurePriceList (
    ResourceId VARCHAR(100),
    ResourceName VARCHAR(100),
    unitPrice DECIMAL(18,6),
    reservationTerm VARCHAR(20)
);

-- 插入测试数据
INSERT INTO AzurePriceList VALUES
('RES001', 'VM Standard D2s v3', 0.10, NULL),
('RES001', 'VM Standard D2s v3', 864.00, '1 Year'),
('RES001', 'VM Standard D2s v3', 1555.20, '3 Years'),
('RES002', 'Storage Account LRS', 0.023, NULL),
('RES002', 'Storage Account LRS', 199.68, '1 Year'),
('RES002', 'Storage Account LRS', 359.42, '3 Years');

方法一：使用PIVOT实现 #

SELECT 
    ResourceId,
    ResourceName,
    MAX(CASE WHEN reservationTerm IS NULL THEN unitPrice END) AS RegularUnitPrice,
    [1 Year] AS Reservation1YearPrice,
    [3 Years] AS Reservation3YearPrice
FROM (
    -- 子查询作为PIVOT的源表，避免直接用原表引发的语法问题
    SELECT ResourceId, ResourceName, unitPrice, reservationTerm
    FROM AzurePriceList
) AS SourceTable
PIVOT (
    -- 用MAX聚合，因为每个资源对应每个term只有一行数据
    MAX(unitPrice)
    -- 指定要转成列的reservationTerm值
    FOR reservationTerm IN ([1 Year], [3 Years])
) AS PivotTable
-- 按资源分组，确保同一资源的所有价格合并到一行
GROUP BY ResourceId, ResourceName, [1 Year], [3 Years];

之前PIVOT报错大概率是因为没有用子查询包裹原表，或者未处理reservationTerm为NULL的常规单价行。这里通过子查询作为源表，再结合聚合函数提取常规单价，就能正确输出目标结构。

方法二：条件聚合（更灵活） #

SELECT 
    ResourceId,
    ResourceName,
    -- 提取常规单价（reservationTerm为NULL的行）
    MAX(CASE WHEN reservationTerm IS NULL THEN unitPrice END) AS RegularUnitPrice,
    -- 提取1年预留价格
    MAX(CASE WHEN reservationTerm = '1 Year' THEN unitPrice END) AS Reservation1YearPrice,
    -- 提取3年预留价格
    MAX(CASE WHEN reservationTerm = '3 Years' THEN unitPrice END) AS Reservation3YearPrice
FROM AzurePriceList
-- 按资源唯一标识分组，把同一资源的多行数据聚合为一行
GROUP BY ResourceId, ResourceName;

之前条件聚合结果分散是因为没加正确的GROUP BY，或者没使用聚合函数（比如MAX/MIN）把同一资源的不同行值合并。这种方法比PIVOT更灵活，后续如果要新增其他预留期限的列，直接加对应CASE语句即可。

内容的提问来源于stack exchange，提问作者Francesco Mantovani