Converting multiple row values to column name using Postgres

Question

I have a table like this:

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

I want to transform into like

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

I tried to use case statements but am not able to achieve the result.

D-Shih · Accepted Answer · 2018-07-21T08:09:50.460

You can try to use CASE WHEN with MAX function to make pivot table.

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);

Query

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

NOTE

Avoid use keyword to be your tables column name, like values or year... you can use another name instead of that

2SRTVF · Answer 2 · 2018-07-21T13:05:09.233

Before:

stackoverflow.com: Dynamic alternative to pivot with CASE and GROUP BY

If you can "json" better choose.

with T0 as (
SELECT
  State,
  city,
  series || '_' || trim(both ' ' from to_char(year, '9999')) series_year,
  max(values) values_max
FROM T
group by 
  1,2,3
order by 
  1,2,3
)
select 
  State,
  city,
  jsonb_object_agg(series_year, values_max) AS data_json
from T0
group by
  1,2

state | city | data_json                                                                                       
:---- | :--- | :-----------------------------------------------------------------------------------------------
s2    | c1   | {"se_1_1980": 1, "se_1_1981": 2, "se_1_1982": 3, "se_2_1980": 4, "se_2_1981": 5, "se_2_1982": 6}
s2    | c2   | {"se_1_1980": 1, "se_1_1981": 2, "se_1_1982": 3, "se_2_1980": 4, "se_2_1981": 5, "se_2_1982": 6}
s1    | c2   | {"se_1_1980": 1, "se_1_1981": 2, "se_1_1982": 3, "se_2_1980": 4, "se_2_1981": 5, "se_2_1982": 6}
s1    | c1   | {"se_1_1980": 1, "se_1_1981": 2, "se_1_1982": 3, "se_2_1980": 4, "se_2_1981": 5, "se_2_1982": 6}

db<>fiddle here

User Crosstab

info

-- info
SELECT
  state  || '_' || city state_city,
  series || '_' || trim(both ' ' from to_char(year, '9999')) series_year,
  max(values) values_max
FROM T
group by 
  1,2
order by 
  1,2

state_city | series_year | values_max
:--------- | :---------- | ---------:
s1_c1      | se_1_1980   |          1
s1_c1      | se_1_1981   |          2
s1_c1      | se_1_1982   |          3
s1_c1      | se_2_1980   |          4
s1_c1      | se_2_1981   |          5
s1_c1      | se_2_1982   |          6
s1_c2      | se_1_1980   |          1
s1_c2      | se_1_1981   |          2
s1_c2      | se_1_1982   |          3
s1_c2      | se_2_1980   |          4
s1_c2      | se_2_1981   |          5
s1_c2      | se_2_1982   |          6
s2_c1      | se_1_1980   |          1
s2_c1      | se_1_1981   |          2
s2_c1      | se_1_1982   |          3
s2_c1      | se_2_1980   |          4
s2_c1      | se_2_1981   |          5
s2_c1      | se_2_1982   |          6
s2_c2      | se_1_1980   |          1
s2_c2      | se_1_1981   |          2
s2_c2      | se_1_1982   |          3
s2_c2      | se_2_1980   |          4
s2_c2      | se_2_1981   |          5
s2_c2      | se_2_1982   |          6

info

-- info
SELECT distinct  series || '_' || trim(both ' ' from to_char(year, '9999')) series_year FROM T order by 1

| series_year |
| :---------- |
| se_1_1980   |
| se_1_1981   |
| se_1_1982   |
| se_2_1980   |
| se_2_1981   |
| se_2_1982   |

with T2 as(
select 
  *
from crosstab(
  '
SELECT
  state  || ''_'' || city state_city,
  series || ''_'' || trim(both '' '' from to_char(year, ''9999'')) series_year,
  max(values) values_max
FROM T group by 1,2 order by 1,2',
  'SELECT distinct series || ''_'' || trim(both '' '' from to_char(year, ''9999'')) series_year FROM T order by 1'
  )
as newtable (
  state_city text,
  se_1_1980 text,
  se_1_1981 text,
  se_1_1982 text,
  se_2_1980 text,
  se_2_1981 text,
  se_2_1982 text
  )
)
select 
  split_part(state_city,'_',1) state,
  split_part(state_city,'_',2) city,
  se_1_1980,
  se_1_1981,
  se_1_1982,
  se_2_1980,
  se_2_1981,
  se_2_1982
from T2

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

db<>fiddle here

@2SRTVF . . . Interesting solution. If I were to use Postgres specific functionality, I think my first thought would be `crosstab`. — Gordon Linoff, Jul 21 '18 at 11:17

Converting multiple row values to column name using Postgres

2 Answers2