Geek Answers Handbook

From one row to several columns and rows

I have a SQL Server table with a name Testwith sample data:

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

I would like to create the following 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. Figures, for example, for data only, are dynamic.

+4
source share
3 answers
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
+4
source

CROSS APPLYcan be used for UNPIVOTdata:

SELECT [LineNo], [Month], Base, Budget
FROM test
CROSS APPLY(VALUES -- unpivot columns into rows
    ('January', BaseJanuary, BudgetJanuary) -- generate row for jan
  , ('February', BaseFebruary, BudgetFebruary) -- generate row for feb
) ca ([Month], Base, Budget)
+4
source 
SELECT LineNo, 'January' AS Month, BaseJanuary, BudgetFebruary
FROM Test
ORDER BY LineNo
UNION ALL
SELECT LineNo, 'February' AS Month, BaseFebruary, BudgetFebruary 
FROM Test
ORDER BY LineNo
0
source

More articles:


All Articles