Subject Re: [firebird-support] crosstab pivot query
Author Svein Erling Tysvær
Post date 2017-09-26T08:32:40Z
Hi Olaf!

Not quite crosstab, but knowing there is a maximum of 31 days in a month, you can simulate:

with tmpPerson(Person) as 
(select distinct person
 from tableA
 where month = 9),
tmpDays("day", person, sign) as
(select "day", person, sign
 from tableA
 where month = 9)
select p.person, list(d1.sign) day1, list(d2.sign) day2, list(d3.sign) day3, ... list(d30.sign) day30, list(d31.sign) day31
from tmpPerson p
left join tmpDays d1 on p.person = d1.person and d1."day" = 1
left join tmpDays d2 on p.person = d1.person and d1."day" = 2
left join tmpDays d3 on p.person = d1.person and d1."day" = 3
...
left join tmpDays d30 on p.person = d1.person and d1."day" = 30
left join tmpDays d31 on p.person = d1.person and d1."day" = 31 /*Not applicable for September, but I assume you want other months as well and it doesn't harm...*/
group by p.person

If no person has two records any given day, then you don't need 'list' and 'group by'.

Since SQL isn't very suitable for crosstab (*), I normally prefer to select - possibly with grouping - in Firebird and "crosstabbing" in Excel.

HTH,
Set

(*) My experience is more or less limited to Fb 2.5, but I cannot imagine how to prepare a statement with an unknown number of columns, in theory that could also mean an unknown number of parameters