OK. Not sure if this is the best solution but it seems to work.

select [fisyear], [category], 
(select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=7 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as jul,
(select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=8 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as aug,
(select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=9 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as sep
from [rta-revenues] as crsA
group by [fisyear],[category]
order by FISYEAR, CATEGORY

Note it does not use the CASE statement we had been playing with. What I don't understand is why the former did not work and why it insisted on having receiveddate in the group by clause, since it was not actually in the select list. It was just included in a function within the select list. Oh well, thanks for your help. As always, you made me think.


>No, remove from GROUP BY and SELECT column list
>
>>Yeah. I assume you mean remove from the Group By list. But this code:
>>

>select crsB.[category], jul, [fisyear]
>>from (select distinct [category] from [RTA-Revenues]) as crsA 
>>inner join (
>>select [fisYear], [category],
>>CASE WHEN month([receiveddate])=7 THEN sum([AMOUNT]) ELSE CAST(0 AS numeric(12,2)) END as Jul
>>from [rta-revenues]
>>group by [FISYEAR], [CATEGORY]) as crsB
>>on crsA.[CATEGORY] = crsB.[CATEGORY]
>>order by [FISYEAR], [CATEGORY]
>>

>>
>>produces this error.
>>
>>Msg 8120, Level 16, State 1, Line 33
>>Column 'rta-revenues.RECEIVEDDATE' is invalid in the select list because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>>
>>
>>So what to do?
>>
>>
>>
>>>Remove columns you don't want to group on.
>>>
>>>>Thanks Sergy. You are amazing! That gets me to here.
>>>>

>>>>select crsB.category, jul, [fisyear]
>>>>from (select distinct category from [RTA-Revenues]) as crsA 
>>>>inner join (
>>>>select [fisYear], [category],
>>>>CASE WHEN month([receiveddate])=7 THEN sum(AMOUNT) ELSE CAST(0 AS numeric(12,2)) END as Jul
>>>>from [rta-revenues]
>>>>group by receiveddate, FISYEAR, CATEGORY) as crsB
>>>>on crsA.CATEGORY = crsB.CATEGORY
>>>>order by FISYEAR
>>>>

>>>>
>>>>Note that sqlserver is insisting on having receiveddate in the group by clause. Something I did not have before. As above it runs but produces multiple rows for each fisyear/category combination. Something I do not want.