October 8, 2010 at 3:29 am
I am using following pivot table query :
select CASE WHEN calendar_year=2009 THEN sum(amount1) ELSE 0 END as amount_1,
CASE WHEN calendar_year=2010 THEN sum(amount2) ELSE 0 END as amount_2,
substring(calendar_month_name,1,3) as MonthName,
from table_names
where clause ....
group by attribute-list
This query returns following resultset
amount_1 | amount_2
+++++++++++++++++++++
10 | 0
20 | 0
30 | 0
0 | 5
0 | 10
0 | 10
+++++++++++++++++++++++
But I need resultset as follows since In the final resultset I want division of amount_2 by amount_1
10 5
20 10
30 10
How should I modify my query so that It will return the above mentioned resultset ?
Please help.
October 8, 2010 at 5:20 am
You're very close:
select SUM(CASE WHEN calendar_year=2009 THEN amount1 ELSE 0 END) as amount_1,
SUM(CASE WHEN calendar_year=2010 THEN amount2 ELSE 0 END) as amount_2,
substring(calendar_month_name,1,3) as MonthName,
from table_names
where clause ....
group by attribute-list
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply