Subject Crosstab SQL statement in FireBird
Author Bhavbhuti Nathwani
Hi All

I have the following tables and fields (shortened list):

iID, cCode

iID, iNo, cBk

iID, iPID, iItemID, bQty

I would like to have something like this for SQL Pass Through:

SELECT tR.iNo, tR.cBk, sR.1stCodebQty, sR.2ndCodebQty, sR.3rdCodebQty, ...
FROM tRequisitions tR
JOIN sRequisitionItems sR ON sR.iPID = tR.iID
JOIN mItems mI ON mI.iID = sR.iItemID
ORDER BY tR.iNo, tR.cBk, sR.iSrNo

but just one line for each tRequisition, with columns for each
mI.cCode with it's respective SUM(sR.bQty) like below:

1, A, 10, 0, 5, ...

Hope I have been able to explain my requirements. I guess I can CASE
WHEN, but that makes the statement long, if there is an efficient way,
which can also help when newer mItems.cCodes are added.