Search code examples
sqlsql-serverunpivot

Single row to multiple columns and rows

I have a SQL Server table called Test with this sample data:

LineNo  BaseJanuary BaseFebruary    BudgetJanuary   BudgetFebruary
1       10000       20000           30000           40000
2       70000       80000           90000           100000

I would like to create the below structure in a SQL Server view (or temporary table etc.) but I'm stuck... any ideas/suggestions would be appreciated!

LineNo  Month       Base    Budget
1       January     10000   30000
2       January     70000   90000
1       February    20000   40000
2       February    80000   100000

Note: The numbers are for example only, the data is dynamic.

Solution 
  • select LineNo,
       'January' as Month,
       BaseJanuary as Base,
       BudgetJanuary as Budget
  from test
union
select LineNo,
       'February' as Month,
       BaseFebruary as Base,
       BudgetFebruary as Budget
  from test
 order by LineNo, Month