SQL Crosstabs with Paradox

How to obtain a crosstab output with Paradox with Local SQL

... using a few simple SQL tricks.
Paradox and Borland Database Engine (BDE) really does't support crosstab queries nor the SQL functionalities usually needed for creating crosstabs.
As Paradox does not support CASE conditions nor subqueries as arguments for the FROM predicate nor correlated subqueries for the SELECT predicate, crosstabs have to rely on the Local SQL ability of in-statement referencing disk stored SQL statements as equivalent of subqueries or VIEWs.
The crosstab solution is to build and add the aggregated columns step by step using JOINs on these intermediate data generating stored SQL statements.
The SQL has been tested valid with Delphi Paradox SQL compliant solutions (e.g. PdxEditor) using BDE 5.2.0.2 as the database middleware.

Consider having a computer leasing business with an Assets list linked with two key-lists for decoding the TypeName (table: AssetTypes) and the StatusName (table: AssetStatus).

And we would like to create a crosstab like this:

To get through to the final crosstab presentation a series of SQL statements have to be created and disk stored to be accessible by the final crosstab generating query. None of the intermediate data are disk stored - only the SQL-statements (i.e.: *.sql text files).
The statements are stored in e.g. a subfolder \SQL relative to the database directory. This location is used by the column generating and final SQL statements.

First we will generate a de-coded Assets list for simpler access for the successive queries:

/* This is the AssetsDecoded.sql extracting Assets decoded for TypeName and Status */
SELECT DISTINCT
  TypeName
 ,StatusName
 ,AssetID
FROM AssetTypes
 LEFT JOIN Assets ON AssetType = AssetTypes.ID
 JOIN AssetStatus ON AssetStatus.ID = Assets.AssetStatus
;

transcribing the Assets list to this:

Next, we will create the left hand category column, and to ensure independence of possible empty groups in the Assets list, the grouping is based on every possible values from the TypeName list (AssetTypes):

/* This is the AssetsXTab0.sql generating the left hand categories */
SELECT DISTINCT
  TypeName
FROM AssetTypes
;


that will generate this:

The flowing SQL statements will generate each of the value columns for the resulting crosstab:

/* This is the AssetsXTabA.sql generating the Inventory aggregate column */
SELECT DISTINCT
  TypeName
 ,COUNT(AssetID) AS Inventory
FROM
 "SQL\AssetsDecoded.sql"
WHERE
 StatusName = 'Inventory'
GROUP BY TypeName
;


/* This is the AssetsXTabB.sql generating the Shipped aggregate column */
SELECT DISTINCT
  TypeName
 ,COUNT(AssetID) AS Shipped
FROM
 "SQL\AssetsDecoded.sql"
WHERE
 StatusName = 'Shipped'
GROUP BY TypeName
;


/* This is the AssetsXTabC.sql generating the Deployed aggregate column */
SELECT DISTINCT
  TypeName
 ,COUNT(AssetID) AS Deployed
FROM
 "SQL\AssetsDecoded.sql"
WHERE
 StatusName = 'Deployed'
GROUP BY TypeName
;


/* This is the AssetsXTabDTotal.sql generating the Totals aggregate column */
SELECT DISTINCT
  TypeName
 ,COUNT(AssetID) AS Total
FROM
 "SQL\AssetsDecoded.sql"
GROUP BY TypeName
;

And to run the final crosstab query just execute this:

/* This is AssetsXTabFinal.sql calling all other SQLs and generating final crosstab */
SELECT DISTINCT
  a.TypeName
 ,Inventory
 ,Shipped
 ,Deployed
 ,Total
FROM
 "SQL\AssetsXTab0.sql" a
 LEFT JOIN "SQL\AssetsXTabA.sql" xa ON xa.TypeName = a.TypeName
 LEFT JOIN "SQL\AssetsXTabB.sql" xb ON xb.TypeName = a.TypeName
 LEFT JOIN "SQL\AssetsXTabC.sql" xc ON xc.TypeName = a.TypeName
 LEFT JOIN "SQL\AssetsXTabDTotal.sql" xt ON xt.TypeName = a.TypeName
;


and you will get e.g. this:

The SQL code easily adapts to quantitative values like SUM as well as statistics as MIN, MAX, AVG just changing the COUNT function as desired in the four value column SQL statements.
So, though a little laborious for ad hoc crosstabs, if you do need to regularly query some dynamic Paradox data and don't mind about the 'missing' zeros for the all-NULL value counts, this may be a solution.