Transposing multiple related columns-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Unpivot acts similar as union,Use union all in your case

SELECT month,
       year,
       po1 AS PO,
       ro1 AS RO,
       mo1 AS MO
FROM   test
UNION ALL
SELECT month,
       year,
       po2,
       ro2,
       mo2
FROM   test
UNION ALL
SELECT month,
       year,
       0,
       0,
       mo2
FROM   test