I have the below table
+------+------+---+---+---+
| type | year | a | b | c |
+------+------+---+---+---+
| x | 2015 | 1 | 1 | 1 |
| x | 2015 | 2 | 2 | 2 |
| x | 2017 | 3 | 3 | 3 |
| y | 2016 | 1 | 1 | 1 |
| y | 2017 | 2 | 2 | 2 |
| z | 2015 | 1 | 1 | 1 |
| z | 2016 | 3 | 3 | 3 |
+------+------+---+---+---+
The expected result must be as follows
+------+------+---+---+---+
| type | year | x | y | z |
+------+------+---+---+---+
| a | 2015 | 3 | 0 | 1 |
| a | 2016 | 0 | 1 | 3 |
| a | 2017 | 3 | 2 | 0 |
| b | 2015 | 3 | 2 | 0 |
| b | 2016 | 0 | 1 | 3 |
| b | 2017 | 3 | 2 | 0 |
| c | 2015 | 3 | 0 | 1 |
| c | 2016 | 0 | 1 | 3 |
| c | 2017 | 3 | 2 | 0 |
+------+------+---+---+---+
So far, I could write the following query to obtain the result using simple group by in a pivot table manner, but i need to display the result in the unpivot manner as my expected result shown as above.
select type, year, sum(a) as a, sum(b) as b, sum(c) as c from table group by type,year;
The result of the above query is a valuable result for me but in a different format
+------+------+---+---+---+
| type | year | a | b | c |
+------+------+---+---+---+
| x | 2015 | 3 | 3 | 3 |
| x | 2017 | 3 | 3 | 3 |
| y | 2016 | 1 | 1 | 1 |
| y | 2017 | 2 | 2 | 2 |
| z | 2015 | 1 | 1 | 1 |
| z | 2016 | 3 | 3 | 3 |
+------+------+---+---+---+
You can unpivot using union all and then reaggregate:
select col_type, year,
sum(case when type = 'x' then val end) as x,
sum(case when type = 'y' then val end) as y,
sum(case when type = 'z' then val end) as z
from (select type, year, 'a' as col_type, a as val from t union all
select type, year, 'b' as col_type, b as val from t union all
select type, year, 'c' as col_type, c as val from t
) x
group by col_type, year;