By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

SQL crosstab results in numbers on the 'diagonal'

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Use GROUP BY MeasName ;

--
Anith
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.