如何使用SQL的PIVOT函数按年份实现行转列？

我尝试用SQL的PIVOT函数按年份把数据行转换为列，但没能成功实现，恳请各位帮忙。以下是我的原始数据、当前代码，预期结果是将每个Type+Name组合对应的各年份Cost汇总后单独作为一列展示。

原始数据 #

Type | Name |  Cost   | Year
A    | NDA  | $385.00 | FY22
A    | ING  | $342.00 | FY21
A    | IEG  | $360.00 | FY21
A    | ERT  | $346.00 | FY21
A    | OVA  | $176.00 | FY20
A    | ZIO  | $310.00 | FY20
A    | ZIO  | $301.00 | FY22
A    | ZIO  | $237.00 | FY21
A    | ZIU  | $398.00 | FY21
A    | ZIU  | $338.00 | FY20
A    | ZIU  | $101.00 | FY22
B    | RLA  | $319.00 | FY22
B    | RLA  | $148.00 | FY20
B    | RLA  | $206.00 | FY21
B    | ACK  | $224.00 | FY22
B    | EZA  | $282.00 | FY21
B    | EON  | $187.00 | FY21
B    | EON  | $250.00 | FY20
B    | EGG  | $297.00 | FY20

当前尝试的代码 #

select Type, Name, sum(price) as Cost, Year
from
(
  select Type, Name, price, Year
  from ProductDB
) a
pivot
(
 ????
) as PivotTable;

正确的PIVOT实现方案 #

以SQL Server为例，你需要在PIVOT子句中指定聚合函数、要转成列的字段以及具体的列值。这里我们按Type和Name分组，对Cost求和，把不同的Year值转成列：

SELECT Type, Name, FY20, FY21, FY22
FROM (
    SELECT Type, Name, Cost, Year
    FROM ProductDB
) AS SourceTable
PIVOT (
    SUM(Cost)  -- 聚合函数，对Cost求和
    FOR Year IN (FY20, FY21, FY22)  -- 要转成列的Year值
) AS PivotTable;

补充说明 #

1. 子查询只需要取出Type、Name、Cost、Year这几个必要字段，不需要提前聚合
2. PIVOT内部的SUM(Cost)会自动按Type和Name分组，对每个年份的Cost求和
3. FOR Year IN (FY20, FY21, FY22)明确指定要转成列的年份值，这些值会作为结果集的列名
4. 如果你的数据库是MySQL（不支持原生PIVOT），可以用条件聚合实现相同效果：

SELECT 
    Type,
    Name,
    SUM(CASE WHEN Year = 'FY20' THEN Cost ELSE 0 END) AS FY20,
    SUM(CASE WHEN Year = 'FY21' THEN Cost ELSE 0 END) AS FY21,
    SUM(CASE WHEN Year = 'FY22' THEN Cost ELSE 0 END) AS FY22
FROM ProductDB
GROUP BY Type, Name;

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