Transpose

Hi Everyone

I have a dataset and I want to transpose it. Below is the input.TIA

Person M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10

I want output like this

Person Month revenue
P1 M1
P1 M2
P1 M3
P1 M4
P1 M5
P1 M6
P1 M7
P1 M8
P1 M9
P1 M10
P1 M11
P1 M12
P2 M1
P2 M2
P2 M3
P2 M4
P2 M5
September 3rd, 2015 2:52am

Hi vedprakash_007,

To achieve your requirement, we can use the  UNPIVOT relational operator to change a table-valued expression into another table. The following query is for your reference:
select u.person, u.month, u.revenue
from Employee
unpivot
(
  revenue
  for month in (M1, M2, M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
) u;

Reference:
Using PIVOT and UNPIVOT

Thanks,
Katherine Xiong

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 3:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics