sql - 使用数据透视SQL将列转换为行

我有下表“ total_points”

YEAR | COUNTRY | POINTS
----------------------
2014 | UK      | 100
2014 | ITALY   | 200
2015 | UK      | 100
2015 | ITALY   | 100
2016 | UK      | 300
2016 | ITALY   | 300

我正在尝试使用数据透视表转换为以下内容

YEAR | UK | ITALY
----------------
2014 | 100 | 200
2015 | 100 | 100
2016 | 300 | 300

我的代码如下，并且出现语法错误new'pivot'。知道我在哪里犯错了吗？

CREATE VIEW total_club_points_pivoted AS
select *
from
(
    select YEAR, COUNTRY, POINTS
    from total_points
) src
pivot
(
    POINTS
    for COUNTRY in (['UK'], ['ITALY'])
) piv;

最佳答案

您需要删除'：

select *
from
(
    select YEAR, COUNTRY, POINTS
    from total_points
) src
pivot
(
    MAX(POINTS) for COUNTRY in ([UK], [ITALY])  -- here removed ' + added agg func
) piv;

DBFiddle Demo



编辑：

SQLite等效项：

SELECT year,
     MAX(CASE WHEN Country='UK' THEN Points END) AS "UK",
     MAX(CASE WHEN Country='ITALY' THEN Points END) AS "Italy"
FROM total_points
GROUP BY year;

DBFiddle Demo2