By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table us_death
( state char(2)
, year smallint
, metric integer
);
insert into us_death values
('CA', 1999, 150), ('CA', 2000, 200), ('CA', 2001, 100),
('WS', 1999, 50), ('WS', 2001, 200),
('OH', 1999, 40), ('OH', 2000, 50), ('OH', 2001, 40);
8 rows affected
select state
, sum(case when year=1999 then metric else null end) as Y1999
, sum(case when year=2000 then metric else null end) as Y2000
, sum(case when year=2001 then metric else null end) as Y2001
from us_death
where year between 1999 and 2001
group by state
order by state asc;
| state | Y1999 | Y2000 | Y2001 |
|---|---|---|---|
| CA | 150 | 200 | 100 |
| OH | 40 | 50 | 40 |
| WS | 50 | null | 200 |
Warning: Null value is eliminated by an aggregate or other SET operation.
select state
, sum(case when year=1999 then metric else 0 end) as Y1999
, sum(case when year=2000 then metric else 0 end) as Y2000
, sum(case when year=2001 then metric else 0 end) as Y2001
from us_death
where year between 1999 and 2001
group by state
order by state asc;
| state | Y1999 | Y2000 | Y2001 |
|---|---|---|---|
| CA | 150 | 200 | 100 |
| OH | 40 | 50 | 40 |
| WS | 50 | 0 | 200 |