SQL Pivot Query

Hi

I am trying to pivot data in the following format

Year           Month      Amount      Budget
2011      Jan              100              90
2011      Feb              40              30
2011      Mar              60              67
2011      Mar              15                5

to get a result like:
Year          Act_Bud      Jan      Feb      Mar      Apr
2011      Actual      100      40      20      100
2011      Budget      90      30      20      68
2012      Actual      94      34      14      94
2012      Budget      84      24      14      62

I used the following SQL Query when the raw data only had an Actual column:
Select * From [Pivot1] as S Pivot (Sum(Amount) For [Month] In (Jan,Feb, Mar, Apr)) As P
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
try the below code

/*
Year           Month      Amount      Budget
2011      Jan              100              90
2011      Feb              40              30
2011      Mar              60              67
2011      Mar              15                5 
*/

/*
Year          Act_Bud      Jan      Feb      Mar      Apr
2011      Actual      100      40      20      100
2011      Budget      90      30      20      68
2012      Actual      94      34      14      94
2012      Budget      84      24      14      62 
*/

DECLARE @t TABLE 
(
 Year INT, MONTH CHAR(3), Amount INT, Budget INT
)
INSERT INTO @t VALUES
 (2011,'Jan',100,90)
,(2011,'Feb',40,30)
,(2011,'Mar',60,67)
,(2012,'Jan',2100,90)
,(2012,'Feb',240,30)
,(2012,'Mar',260,67)

SELECT Year,'Actual' AS Act_BUD,[jan],[Feb],[mar],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC] FROM
(SELECT Year,Month,Amount
FROM @t) P
PIVOT
(
    SUM(Amount) FOR Month IN([jan],[Feb],[mar],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])
) PVT
UNION ALL
SELECT Year,'Budget' AS Act_BUD,[jan],[Feb],[mar],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC] FROM
(SELECT Year,Month,Budget
FROM @t) P
PIVOT
(
    SUM(Budget) FOR Month IN([jan],[Feb],[mar],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])
) PVT

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much. That worked perfectly
0
All Courses

From novice to tech pro — start learning today.