SQL count when equation of two columns are true

I have a sheet with rows (id and year). See below:

id	year
101	2002
101	2006
101	2010
101	2014
101	2018
102	2002
102	2006
102	2010
102	2014
103	2010

I simply want to regroup and reformat my table to look like this:

id	2002	2006	2010	2014	2018
101	1	1	1	1	1
102	1	1	1	1	0
103	0	0	1	0	0

In other words, whenever there is an id with a specific year it will show as a “1” in a field corresponding to that year. Note, that in the sheet there are no other years than the ones above.

I have managed to get the sheet reformatted by

select 
    id, 
    null as '2002', null as '2006', null as '2010', 
    null as '2014', null as '2018' 
from 
    year_sheet 
order by 
    id

But how to count and fill in the values for each year I don’t find any solution.

Can someone help?

Thanks

Answer

You can use conditional aggregation:

SELECT id
     , COUNT(CASE WHEN year = 2002 THEN 1 END) AS "2002"
     , COUNT(CASE WHEN year = 2006 THEN 1 END) AS "2006"
     , COUNT(CASE WHEN year = 2010 THEN 1 END) AS "2010"
     , COUNT(CASE WHEN year = 2014 THEN 1 END) AS "2014"
     , COUNT(CASE WHEN year = 2018 THEN 1 END) AS "2018"
FROM t
GROUP BY id
ORDER BY id

SQL Fiddle