Two methods for creating pivot tables with Firebird 2.5

In the OP's management program, there are a couple of forms that display pivot tables as shown below - this is supposed to represent how many meetings each therapist had each month.

Therapist	1	2	3	4	5	6	7	8	9	10	11	12
John	4		13	6	12	1		1	1	4	8	1
Judy	12	11	10	9	8	7	6	5	4	3	2	1

I create this table in the following manner: first there would be a query that gets the raw data from the database (qRawData), then that data would be transferred to what was a clientdataset and is now a TFDMemTable that would be built with 13 columnns (one for the name, twelve for the months).

qRawData.sql statement:
 select therapists.name, extract (month from meetings.perfdate) as monthnum, 
        count (meetings.id) as meet
 from therapists
 inner join meetings on meetings.therapist = therapists.id
 where meetings.activity = :p1
 and meetings.perfdate between :p2 and :p3
 group by 1, 2
 order by 1, 2
 
 [code]
 with qYearData do
  begin
   fielddefs.add ('therapist', ftWideString, 24, false);
   for m:= 1 to 12 do
    fielddefs.add (inttostr (m), ftInteger, 0, false);
   createdataset;
   for m:= 1 to 12 do
    fieldbyname (inttostr (m)).DisplayWidth:= 6;
   open
  end;

 with qRawData do
  begin
   close;
   parambyname ('p1').asinteger:= activity;
   parambyname ('p2').asdate:= encodedate (year, 1, 1);
   parambyname ('p3').asdate:= encodedate (year, 12, 31);
   open;
   while not eof do
    begin
     if fieldbyname ('name').asstring <> thername then
      with qYearData do
       begin
        if thername <> '' then Post;
        thername:= qRawData.fieldbyname ('name').asstring;
        append;
        fieldbyname ('therapist').asstring:= thername;
       end;
     amonth:= fieldbyname ('monthnum').asinteger mod 12;
     if amonth = 0 then amonth:= 12;
     qYearData.fieldbyname (inttostr (amonth)).asinteger:=
          fieldbyname ('meet').asinteger;
     next
    end;
   close
  end;

I wondered whether there was a better way of doing this. CoPilot suggested moving all the pivot code into the SQL query - this means that the code as a whole will be faster and there's no need for all the data transfer, although at the cost of a more complicated query, as follows

select therapists.name, 
sum (case when extract (month from m.perfdate) = 1 then 1 else 0 end) as m01,
sum (case when extract (month from m.perfdate) = 2 then 1 else 0 end) as m02,
sum (case when extract (month from m.perfdate) = 3 then 1 else 0 end) as m03,
sum (case when extract (month from m.perfdate) = 4 then 1 else 0 end) as m04,
sum (case when extract (month from m.perfdate) = 5 then 1 else 0 end) as m05,
sum (case when extract (month from m.perfdate) = 6 then 1 else 0 end) as m06,
sum (case when extract (month from m.perfdate) = 7 then 1 else 0 end) as m07,
sum (case when extract (month from m.perfdate) = 8 then 1 else 0 end) as m08,
sum (case when extract (month from m.perfdate) = 9 then 1 else 0 end) as m09,
sum (case when extract (month from m.perfdate) = 10 then 1 else 0 end) as m010,
sum (case when extract (month from m.perfdate) = 11 then 1 else 0 end) as m011,
sum (case when extract (month from m.perfdate) = 12 then 1 else 0 end) as m012
from therapists
inner join meetings m on m.therapist = therapists.id
where m.activity = :p1
and m.perfdate between :p2 and :p3
group by 1
order by 1

This is the sort of code that I write once and never look at again. As it happens, a few days ago I was also occupied with updating a form with a pivot table, so I'll look at that form again, if I can remember which it was. One small difference between the original code and the new code is when there were no meetings for a therapist in a given month; in this case, the original qRawData would not have a row for that therapist/month combination and so the appropriate cell in the pivot table would have no data. Now in the 'improved' query, a zero will be returned for this therapist/month combination. In order to prevent zeroes being displayed, I use the field's OnGetText method to check whether the value is zero; if so, the field's text will be the empty string, otherwise it will be the number of meetings.

I'm using Firebird 2.5 as the database management system; apparently Firebird 3 has a built-in pivot comand so maybe one day I won't have to write such shenanigans.

---

This day in blog history:

Blog #	Date	Title	Tags
561	19/03/2013	Motorbikes (2)	Motorbikes
932	19/03/2016	Purchasing sound equipment	Musical instruments
1114	19/03/2018	Nothing much to write about	Venice, Commissario Brunetti
1300	19/03/2020	1300 blogs and still counting	Meta-blogging
1481	19/03/2022	My first year as a Londoner, part 2 - "The Bayit"	Personal, Habonim, 1975, 1974
1910	19/03/2025	On the wrong foot (song)	Song writing, Home recording