Search code examples
sqlsql-server-2008pivotunpivot

Converting Row Into a Column based on Month

I have following input:

JAN_OLD FEB_OLD MAR_OLD APR_OLD MAY_OLD JUNE_OLD  JAN_NEW FEB_NEW MAR_NEW APR_NEW MAY_NEW JUNE_NEW
10        11       12      13      14       15       20      21      22      23      24      25

Disired result set is as below:

JAN New OLD
FEB 20  10
MAR 21  11
APR 22  12
MAY 23  13
JUN 24  14

Can someone suggest how to achieve this?

Solution

  • Multiple Union All or single Cross Apply

    SELECT months,old,new
FROM   Your_table
       CROSS apply (VALUES(jan_old,jan_new,'Jan'),
                          (FEB_OLD,FEB_new,'Feb'),
                          (MAR_OLD,MAR_new,'Mar'),
                          (APR_OLD,APR_new,'Apr'),
                          (MAY_OLD,MAY_new,'may'),
                          (JUNE_OLD,JUNE_new,'Jun'))
         cs (old, new, months)

    If you are not sure about the no. of columns then you may have to use Dynamic sql