Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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),
('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);
24 rows affected

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

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