• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

I see years - I wanted to see the sum of SumOfAmount in rows and columns and years as column headings - what am I doing wrong?

Select Job, 2003,2004,2005,2006,2007,2008,2009,2010,2011
FROM (
SELECT Job,datepart(year,Acedata.Date) as nYears,
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR nYears IN( [2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011])
  ) AS pvt

my table is simple,
it has columns
 date,job,incredit,outdebit

I am using mssql 8 express
0
goodk
Asked:
goodk
1 Solution
 
ralmadaCommented:
you're missing brackets there
Select 	Job, 
	[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011] 
FROM ( 
	SELECT Job,datepart(year,Acedata.Date) as nYears, 
	(InCredit+OutDebit) AS SumOfAmount 
	FROM AceData 
) ps 
PIVOT( 
SUM (SumOfAmount) 
FOR nYears IN([2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011])
  ) AS pvt

Open in new window

0
 
goodkAuthor Commented:
timely and correct!!
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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