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

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

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

CodePudding user response：

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

CodePudding user response：

try using case statement (Conditional statements)

select id, 
case when year=2002 then 1 else 0 end as "2002",
case when year=2006 then 1 else 0 end as "2006",
case when year=2010 then 1 else 0 end as "2010",
case when year=2014 then 1 else 0 end as "2014",
case when year=2018 then 1 else 0 end as "2018"
from table
order by id

assuming that you would like to make the flags as 1 and 0 if the year appears for the id (for every id year appears only once).

In case if you want to count them (for eery id year appears more than once) then try using sum(case when....) as follows

select id, 
sum(case when year=2002 then 1 else 0  end)as "2002",
sum(case when year=2006 then 1 else 0  end)as "2006",
sum(case when year=2010 then 1 else 0  end)as "2010",
sum(case when year=2014 then 1 else 0  end)as "2014",
sum(case when year=2018 then 1 else 0  end)as "2018"
from table
group by id
order by id