While transposing single columns is pretty straight forward I need to transpose a large amount of data with 3 sets of , 10+ related columns needed to be transposed.
create table test
(month int,year int,po1 int,po2 int,ro1 int,ro2 int,mo1 int,mo2 int, mo3 int);
insert into test
values
(5,2013,100,20,10,1,3,4,5),(4,2014,200,30,20,2,4,5,6),(6,2015,200,80,30,3,5,6,7) ;
select * FROM test;
gives
month | year | po1 | po2 | ro1 | ro2 | mo1 | mo2 | mo3 |
---|---|---|---|---|---|---|---|---|
5 | 2013 | 100 | 20 | 10 | 1 | 3 | 4 | 5 |
4 | 2014 | 200 | 30 | 20 | 2 | 4 | 5 | 6 |
6 | 2015 | 200 | 80 | 30 | 3 | 5 | 6 | 7 |
Transposing using UNPIVOT
select
month, year,
PO, RO, MO
from ( SELECT * from test) src
unpivot
( PO for Description in (po1, po2))unpiv1
unpivot
(RO for Description1 in (ro1, ro2)) unpiv2
unpivot
(MO for Description2 in (mo1, mo2, mo3)) unpiv3
order by year
Gives me this
month | year | PO | RO | MO |
---|---|---|---|---|
5 | 2013 | 100 | 10 | 3 |
5 | 2013 | 100 | 10 | 4 |
5 | 2013 | 100 | 10 | 5 |
5 | 2013 | 100 | 1 | 3 |
5 | 2013 | 100 | 1 | 4 |
5 | 2013 | 100 | 1 | 5 |
5 | 2013 | 20 | 10 | 3 |
5 | 2013 | 20 | 10 | 4 |
5 | 2013 | 20 | 10 | 5 |
5 | 2013 | 20 | 1 | 3 |
5 | 2013 | 20 | 1 | 4 |
5 | 2013 | 20 | 1 | 5 |
4 | 2014 | 200 | 20 | 4 |
4 | 2014 | 200 | 20 | 5 |
4 | 2014 | 200 | 20 | 6 |
4 | 2014 | 200 | 2 | 4 |
4 | 2014 | 200 | 2 | 5 |
4 | 2014 | 200 | 2 | 6 |
4 | 2014 | 30 | 20 | 4 |
4 | 2014 | 30 | 20 | 5 |
4 | 2014 | 30 | 20 | 6 |
4 | 2014 | 30 | 2 | 4 |
4 | 2014 | 30 | 2 | 5 |
4 | 2014 | 30 | 2 | 6 |
6 | 2015 | 200 | 30 | 5 |
6 | 2015 | 200 | 30 | 6 |
6 | 2015 | 200 | 30 | 7 |
6 | 2015 | 200 | 3 | 5 |
6 | 2015 | 200 | 3 | 6 |
6 | 2015 | 200 | 3 | 7 |
6 | 2015 | 80 | 30 | 5 |
6 | 2015 | 80 | 30 | 6 |
6 | 2015 | 80 | 30 | 7 |
6 | 2015 | 80 | 3 | 5 |
6 | 2015 | 80 | 3 | 6 |
6 | 2015 | 80 | 3 | 7 |
I will like to turn it to something like this. Is that possible?
month | year | PO | RO | MO |
---|---|---|---|---|
5 | 2013 | 100 | 10 | 3 |
5 | 2013 | 20 | 1 | 4 |
5 | 2013 | 0 | 0 | 5 |
4 | 2014 | 200 | 20 | 4 |
4 | 2014 | 30 | 2 | 5 |
4 | 2014 | 0 | 0 | 6 |
6 | 2015 | 200 | 30 | 5 |
6 | 2015 | 80 | 3 | 6 |
6 | 2015 | 0 | 0 | 7 |
Maybe use a query like below which creates rows as per your design using CROSS APPLY
select month,year,po,ro,mo from
test cross apply
(values (po1,ro1,mo1), (po2,ro2,mo2),(0,0,mo3))v(po,ro,mo)
see demo here