Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

SQL Pivot table

Hi

I use the SQL further down to get the data from the first table below into the
format further on. I now have to add a fourth column called "Budget" and want
to include the sum of these amounts to Jan,Feb,Mar,Apr in the pivot as 4 new columns
How would I do this?

Year      Month      Amount
2011      Jan      100
2011      Feb      40
2011      Mar      60
2011      Mar      15
2011      May      50
2011      Jun      77
2012      Jul      70
2012      Aug      80

Select * From [Pivot1] as S Pivot (Sum(Amount) For [Month] In (Jan,Feb, Mar, Apr)) As P

Year       Jan      Feb      Mar      Apr
2011      100      40      75      NULL
2012      78      140      78      160
0
Murray Brown
Asked:
Murray Brown
  • 6
  • 4
1 Solution
 
x-menIT super heroCommented:
Select * From (select *,
      sum([Amount]) over (partition by year) as budget
 from EE)
 as S Pivot (Sum(Amount) For [Month] In (Jan,Feb, Mar, Apr)) As P
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I get "Invalid object name EE"
My table name is Pivot2
0
 
x-menIT super heroCommented:
Select * From (select *,
      sum([Amount]) over (partition by year) as budget
 from Pivot2)
 as S Pivot (Sum(Amount) For [Month] In (Jan,Feb, Mar, Apr)) As P
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks but still get errors
Msg 8156, Level 16, State 1, Line 4
The column 'budget' was specified multiple times for 'S'.
Msg 8156, Level 16, State 1, Line 4
The column 'budget' was specified multiple times for 'P'.
0
 
x-menIT super heroCommented:
My test:


create table Pivot2 ([Year] int,[Month] char(3),[Amount] int)
insert into Pivot2 VALUES(2011,'Jan',100)
insert into Pivot2 VALUES(2011,'Feb',40)
insert into Pivot2 VALUES(2011,'Mar',60)
insert into Pivot2 VALUES(2011,'Mar',15)
insert into Pivot2 VALUES(2011,'May',50)
insert into Pivot2 VALUES(2011,'Jun',77)
insert into Pivot2 VALUES(2012,'Jul',70)
insert into Pivot2 VALUES(2012,'Aug',80)
insert into Pivot2 VALUES(2012,'Jan',78)
insert into Pivot2 VALUES(2012,'Feb',140)
insert into Pivot2 VALUES(2012,'Mar',78)


Select * From (select *,
      sum([Amount]) over (partition by year) as budget
 from Pivot2)
 as S Pivot (Sum(Amount) For [Month] In (Jan,Feb, Mar, Apr)) As P

Year|budget|Jan|Feb|Mar|Apr
2011|342|100|40|75|NULL
2012|446|78|140|78|NULL

(2 row(s) affected)
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Sorry to take so much of your time but my data has four columns
Year           Month      Amount      Budget
2011      Jan              100              90
2011      Feb         40              30
2011      Mar              60              67
2011      Mar              15                5
0
 
x-menIT super heroCommented:
you lost me.
what are you trying to accomplish?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
A result like the following:

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
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for your help. I will post a new question to set out what I need
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now