sql rows to 2 rows and columns

Question

I have a table with 4 columns right now, project_id, month, forecast, actual.

Each project has 12 rows (for 12 months).

I want to transform this to where each record has 2 rows (actual and forecast) and 12 columns for each of the month?

Is there a way to achieve this easily in sql server? or I have to do a union for the actual and the forecast?

example data: (sorry, I don't know how to put this in a table format here)

project_id | month | forecast | actual
111        | jan   | 35       | 30
111        | feb   | 36       | 31
111        | mar   | 45       | 3
....

desire result

project_id | type     | Jan | Feb | Mar ....
111        | actual   | 30  | 31  | 3
111        | forecast | 35  | 36  | 45

I knew about pivot, just want to know if i have to use union. Looks like I do. thanks — chungtinhlakho, Apr 21 '17 at 16:52

score 2 · Accepted Answer · answered Apr 21 '17 at 13:36

You can use pivot and union all as below:

select * from  (
    select project_id, month, forecast from #yourproject ) s
    pivot (max(forecast) for month in ([jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec])) p
Union all
select * from  (
    select project_id, month, actual from #yourproject ) s
    pivot (max(actual) for month in ([jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec])) p

Your input table:

 create table #yourproject (project_id int, month varchar(3), forecast int, actual int)

 insert into #yourproject (project_id, month, forecast, actual) values
 (111       ,'jan',  35   , 30 )
,(111       ,'feb',  36   , 31 )
,(111       ,'mar',  45   , 3  )
--your records continues

sql rows to 2 rows and columns

1 Answers1