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?

You should tag your questions -- in general -- with the database you are using. — Gordon Linoff, Apr 09 '17 at 19:38
Actualy it just add-in for Excel that makes run SQL for excel tables. — A1exandr Belan, Apr 09 '17 at 19:40

Gordon Linoff · Accepted Answer · 2017-04-09T19:37:51.713

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.

edited Apr 09 '17 at 19:37

answered Apr 09 '17 at 19:36

Gordon Linoff

1,242,037
58
646
786

if I have planty Y2XXX fields? – A1exandr Belan Apr 09 '17 at 19:37
@FFire . . . You can continue adding subqueries -- a spreadsheet can help in constructing the query, if you want. – Gordon Linoff Apr 09 '17 at 19:39

Is it possible "unpivot" table with SQL?

1 Answers1