I have table like this:
account descrip transdate amount 11 service 10/12/2003 2000 11 service 09/10/2003 2500 12 parts 15/7/2003 1520
I want to group it by account and month like this:
account jan feb mar .... service 1500 2000 1750 parts 1250 780 888
Requires Free Membership to View
There was a similar question just last week: A "crosstab" query (27 October 2003). With a couple of these in the Ask The Expert archives, we may not answer similar questions in future. Getting totals by month across the row, however, is a very common denormalization or "crosstab" request, so let's do this one and hopefully the general strategy will be clear for similar questions in future.
The trick to writing a "crosstab" query is to utilize a CASE structure for each column, and a GROUP BY on the key. The drawback is that this query cannot be dynamic. You have to know in advance which columns you want. In the case of totals by month, however, there are only ever twelve months in a year (unless it's an accounting application, where there may be up to fifteen).
select descrip
as Account
, sum(
case when month(transdate) = 01
then amount else 0 end
) as Jan
, sum(
case when month(transdate) = 02
then amount else 0 end
) as Feb
...
, sum(
case when month(transdate) = 12
then amount else 0 end
) as Dec
, sum(amount)
as Total
from yourtable
group
by descrip
Notice how you don't need to use a CASE for the total of all monthly columns across the row; just sum up all amounts.
This was first published in November 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation