As Z T said, Firebird doesn't support cross tabulation. However, I can think of two ways to solve this within Firebird if you know the maximum number of rows.

Solution a)

SELECT <AccountAndOtherFields>,
SUM(CASE WHEN EXTRACT(MONTH FROM <DateField>) = 1 THEN FieldToSum ELSE 0 END) as January,
SUM(CASE WHEN EXTRACT(MONTH FROM <DateField>) = 2 THEN FieldToSum ELSE 0 END) as February,
...
FROM MyTable
GROUP BY <AccountAndOtherFields>

Solution b)

WITH MonthSum AS
(SELECT <AccountAndOtherFields>, EXTRACT(MONTH FROM <DateField>) AS MyMonth, SUM(FieldToSum) as MySum
FROM MyTable
GROUP BY <AccountAndOtherFields>, MyMonth
WHERE MyDate BETWEEN FromDate AND ToDate),
DistinctAccount AS
(SELECT DISTINCT <AccountAndOtherFields>
FROM MyTable
WHERE MyDate BETWEEN FromDate AND ToDate)

SELECT da.<AccountAndOtherFields>, Jan.MySum as January, Feb.MySum as February, ...
FROM DistinctAccount da
LEFT JOIN MonthSum Jan ON da.<AccountAndOtherFields> = Jan.<AccountAndOtherFields> AND Jan.MyMonth = 1
LEFT JOIN MonthSum Feb ON da.<AccountAndOtherFields> = Feb.<AccountAndOtherFields> AND Feb.MyMonth = 2
...

These possible solutions are assuming you want the months as columns rather than rows, but if you know the values of the other fields it should be possible to reverse this.

However, I normally prefer to just do

SELECT <AccountAndOtherFields>, EXTRACT(MONTH FROM <DateField>) AS MyMonth, SUM(FieldToSum) as MySum
FROM MyTable
GROUP BY <AccountAndOtherFields>, MyMonth
WHERE MyDate BETWEEN FromDate AND ToDate

in Firebird, export the result to Excel and then use a PivotTable there. I just find it simpler and quicker to do it this way.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ahmad $arhan ****
Sent: 2. februar 2010 08:48
To: FireBird
Subject: [firebird-support] SQL statment?


hello everybody,

I need to know the SQL statment or whatever for retreving summation of costs for every month of each account ...how to group it

the from have 4 boxes, 2 are (from-date) & (to-date) continuoslly
                                    2 are (from-account) & (to-account) selective.

sample:
                01-01        03-02        05-05    ......etc
jan
feb
mar
aprl
may
jun
.
.
.
etc


thanks in advance.

sarhan