CODEWITHSUNDEEP
sqlsql-serverunpivot
Chilli
Chilli

Reputation: 53

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.

Upvotes: 4

Views: 98

Answers (3)

Mark
Mark

Reputation: 106

CROSS APPLY can be used to UNPIVOT data:

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)

Upvotes: 4

Matt
Matt

Reputation: 15071

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

Upvotes: 0

AngularRat
AngularRat

Reputation: 602

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

Upvotes: 4

Related Questions