Is it possible "unpivot" table with SQL?

Question

I have a table:

id  amount  Y2016   Y2017   Y2018
1   100             1       1
2   200         
3   300     2       
4   400     2       3   
5   500                     1

and need to unpivot it and made some calculation Result table (multiply "amount" on years data):

id  year    amount
1   Y2017   100
1   Y2018   100
3   Y2016   600
4   Y2016   800
4   Y2017   1200
5   Y2018   500

Is it possible? And how?

Answer 1

The simplest way uses union all

select id, 'Y2016' as year, Y2016 * amount as amount
from t
where Y2016 is not null
union all
select id, 'Y2017' as year, Y2017 * amount as amount
from t
where Y2017 is not null
union all
select id, 'Y2018' as year, Y2018 * amount as amount
from t
where Y2018 is not null;

There are other methods (some depending on the database). But for your data, this should be fine.