My pivot table problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

My pivot table problem

Author

Topic

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-02 : 15:34:58

Good day,
I'm practicing making a pivot table I want it to list "years"(2005)etc as column headers and
departments as row headers to indicate the average GPA of each student department of each year.
Here's what I've done so far and the result set:
QUERY:
SELECT Department,
[1] AS '2004',
[2] AS '2005',
[3] AS '2006'

FROM
(SELECT Department,GPA
FROM Student) s
PIVOT
(
AVG(GPA)
FOR GPA IN ([1],[2],[3])
) p
ORDER BY [Department]
GO

RESULTS:

Department 2004 2005 2006
coms NULL NULL 3.000000
edu NULL NULL NULL
hist NULL NULL NULL
math NULL NULL NULL

Can someone tell me why I'm getting these nulls instead of
Average gpa for each department?
Please forgive the result table fields being misaligned

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-02 : 18:52:36

You aren't using the year anywhere in your query, so it can't break down the data by year. Try it this way

SELECT Department, 
[2004], 
[2005], 
[2006] 

FROM 
(SELECT Department, [Year], cast(GPA as float) AS GPA
FROM Student) s 
PIVOT 
( 
AVG(GPA) 
FOR [Year] IN ([2004],[2005],[2006]) 
) p 
ORDER BY [Department]

If you don't have a Year column in the table, just calculate it in the subquery something like this

SELECT Department, 
[2004], 
[2005], 
[2006] 

FROM 
(SELECT Department, year(<yourdatecolumn>) AS [Year], cast(GPA as float) AS GPA
FROM Student) s 
PIVOT 
( 
AVG(GPA) 
FOR [Year] IN ([2004],[2005],[2006]) 
) p 
ORDER BY [Department]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources