sql - 使用 Postgres 将多个行值转换为列名

Question

我有一个这样的表:

State city  year  series values
s1     c1   1980   se_1    1
s1     c1   1981   se_1    2
s1     c1   1982   se_1    3
s1     c1   1980   se_2    4
s1     c1   1981   se_2    5
s1     c1   1982   se_2    6

s1     c2   1980   se_1    1
s1     c2   1981   se_1    2
s1     c2   1982   se_1    3
s1     c2   1980   se_2    4
s1     c2   1981   se_2    5
s1     c2   1982   se_2    6

s2     c1   1980   se_1    1
s2     c1   1981   se_1    2
s2     c1   1982   se_1    3
s2     c1   1980   se_2    4
s2     c1   1981   se_2    5
s2     c1   1982   se_2    6

s2     c2   1980   se_1    1
s2     c2   1981   se_1    2
s2     c2   1982   se_1    3
s2     c2   1980   se_2    4
s2     c2   1981   se_2    5
s2     c2   1982   se_2    6

我要变身成这样

state city se_1_1980 se_1_1981 se_1_1982 se_2_1980 se_2_1981 se_2_1982
s1     c1    1           2         3     4           5         6
s1     c2    1           2         3     4           5         6
s2     c1    1           2         3     4           5         6
s2     c2    1           2         3     4           5         6

我尝试使用 case 语句，但无法获得结果。

Answer 1

您可以尝试使用 CASE WHEN 和 MAX 函数来制作数据透视表。

TestDLL

CREATE TABLE T(
    State VARCHAR(5),
    city VARCHAR(5),
    year  INT,
    series VARCHAR(5),
    values INT
);

INSERT INTO T VALUES ('s1','c1',1980,'se_1',1);
INSERT INTO T VALUES ('s1','c1',1981,'se_1',2);
INSERT INTO T VALUES ('s1','c1',1982,'se_1',3);
INSERT INTO T VALUES ('s1','c1',1980,'se_2',4);
INSERT INTO T VALUES ('s1','c1',1981,'se_2',5);
INSERT INTO T VALUES ('s1','c1',1982,'se_2',6);
INSERT INTO T VALUES ('s1','c2',1980,'se_1',1);
INSERT INTO T VALUES ('s1','c2',1981,'se_1',2);
INSERT INTO T VALUES ('s1','c2',1982,'se_1',3);
INSERT INTO T VALUES ('s1','c2',1980,'se_2',4);
INSERT INTO T VALUES ('s1','c2',1981,'se_2',5);
INSERT INTO T VALUES ('s1','c2',1982,'se_2',6);
INSERT INTO T VALUES ('s2','c1',1980,'se_1',1);
INSERT INTO T VALUES ('s2','c1',1981,'se_1',2);
INSERT INTO T VALUES ('s2','c1',1982,'se_1',3);
INSERT INTO T VALUES ('s2','c1',1980,'se_2',4);
INSERT INTO T VALUES ('s2','c1',1981,'se_2',5);
INSERT INTO T VALUES ('s2','c1',1982,'se_2',6);
INSERT INTO T VALUES ('s2','c2',1980,'se_1',1);
INSERT INTO T VALUES ('s2','c2',1981,'se_1',2);
INSERT INTO T VALUES ('s2','c2',1982,'se_1',3);
INSERT INTO T VALUES ('s2','c2',1980,'se_2',4);
INSERT INTO T VALUES ('s2','c2',1981,'se_2',5);
INSERT INTO T VALUES ('s2','c2',1982,'se_2',6);

查询

SELECT State,
       city,
       MAX(CASE WHEN series ='se_1' AND year = 1980 THEN "values" END) as se_1_1980,
       MAX(CASE WHEN series ='se_1' AND year = 1981 THEN "values" END) as se_1_1981,
       MAX(CASE WHEN series ='se_1' AND year = 1982 THEN "values" END) as se_1_1982,
       MAX(CASE WHEN series ='se_2' AND year = 1980 THEN "values" END) as se_2_1980,
       MAX(CASE WHEN series ='se_2' AND year = 1981 THEN "values" END) as se_2_1981,
       MAX(CASE WHEN series ='se_2' AND year = 1982 THEN "values" END) as se_2_1982 
FROM  T
GROUP BY 
       State,
       city

sqlfiddle

注意

避免使用关键字作为您的表格列名，例如 values 或 year...你可以用另一个名字代替那个