SQL crosstab results in numbers on the 'diagonal'

[mehl]

Hello --

We have annual values for several 'MeasName':
Capital expenditure increment
Growth rate
Subscribers

The table has these fields:
Year
MeasName
MeasValue

We want the result of the crosstab to look like:

MeasName 2005 2006 2007 2008 2009
-----------------------------------------------------------------
CapexIncrement 33 33 41 41 41
GrowthRate 0 .1 .1 .1 .1
Subscribers 42000 46000 50000 55000 60000

The code below results in:
CapexIncremt 33 0 0 0 0
CapexIncremt 0 33 0 0 0
CapexIncremt 0 0 41 0 0
CapexIncremt 0 0 0 41 0
CapexIncremt 0 0 0 0 41
GrowthRate 0 0.1 0 0 0
GrowthRate 0 0 0.1 0 0
GrowthRate 0 0 0 0.1 0
GrowthRate 0 0 0 0 0.1
Subscribers 42000 0 0 0 0
Subscribers 0 46000 0 0 0
Subscribers 0 0 50000 0 0
Subscribers 0 0 0 55000 0
Subscribers 0 0 0 0 60000
SELECT MeasName,
SUM(CASE Yr WHEN 2005 THEN MeasValue ELSE 0 END) AS '2005',
SUM(CASE Yr WHEN 2006 THEN MeasValue ELSE 0 END) AS '2006',
SUM(CASE Yr WHEN 2007 THEN MeasValue ELSE 0 END) AS '2007',
SUM(CASE Yr WHEN 2008 THEN MeasValue ELSE 0 END) AS '2008',
SUM(CASE Yr WHEN 2009 THEN MeasValue ELSE 0 END) AS '2009'
FROM MetricsTime
GROUP BY Yr, MeasName

Can anyone tell me how to change the code to result in the layout we
want?

Thanks for any help.

Larry Mehl

[Anith Sen]

Use GROUP BY MeasName ;

--
Anith