Quick Start

First, we need to procure the data. For maximum functionality, the result should be cubed. The following is an example SQL query for PostgreSQL.

-- name, country, and month are the dimensions, count and sum are the measures.
-- grouping is used to identify if a dimension is subtotaled.
SELECT name,
    country,
    month,
    COUNT(*) AS count,
    SUM(price) AS sum,
    GROUPING(name, country, month) AS grouping
FROM items
GROUP BY DISTINCT CUBE(name, country, month)
-- ORDER BY clause determines the order of the dimensions in the output
ORDER BY name,
    country,
    month

Then, use whatever means to execute the query and fetch the result as an array of array. We can create a pivot table as follows:

use Rekalogika\PivotTable\ArrayTable\ArrayTableFactory;
use Rekalogika\PivotTable\PivotTableTransformer;
use Rekalogika\PivotTable\TableRenderer\BasicTableRenderer;

// Create a data cube from the result set
$cube = ArrayTableFactory::createCube(
    input: $data,
    // must be in the same order as the GROUPING() function in the SQL query:
    dimensionFields: ['name', 'country', 'month'], 
    measureFields: ['count', 'sum'],
    groupingField: 'grouping',
    legends: [
        '@values' => 'Values',
        'name' => 'Item Name',
        'country' => 'Country', 
        'month' => 'Month',
        'count' => 'Count',
        'sum' => 'Sum',
    ],
    subtotalLabels: [
        'name' => 'All Names',
        'country' => 'All Countries',
        'month' => 'All Months',
    ],
);

// Transform the cube to HTML table object
$htmlTable = PivotTableTransformer::transform(
    cube: $cube,
    rows: ['country', 'month'],    // Rows
    columns: ['@values', 'name'],       // Columns  
    measures: ['count', 'sum'],         // Which measures to include
    withSubtotal: ['name', 'country'],  // Add subtotals for these dimensions
);

// Render to HTML string
$html = BasicTableRenderer::render($htmlTable);
echo $html;

The resulting HTML table will look like this:

Country	Month	Count				Sum
		Name				Name
		itemA	itemB	itemC	All names	itemA	itemB	itemC	All names
CN	202501	4	1	5	10	2586.07	434.22	3375.71	6396
	202502	null	5	1	6	null	2575.55	337.29	2912.84
	202503	4	5	3	12	2823.54	2849.01	1488.2	7160.75
	202504	3	null	null	3	1301.73	null	null	1301.73
	202505	null	3	null	3	null	1845.78	null	1845.78
	All months	11	14	9	34	6711.34	7704.56	5201.2	19617.1
JP	202501	1	3	4	8	107.51	1410.79	1765.04	3283.34
	202502	5	1	5	11	3119.07	393.39	2081.02	5593.48
	202503	null	3	5	8	null	1966	3656.16	5622.16
	202504	3	5	2	10	2014.32	2348.93	1042.42	5405.67
	202505	null	1	1	2	null	973.91	725.8	1699.71
	All months	9	13	17	39	5240.9	7093.02	9270.44	21604.36
KR	202501	null	null	2	2	null	null	1134.36	1134.36
	202502	5	3	1	9	1668.59	2239.52	240.94	4149.05
	202503	3	5	4	12	1891.44	1781.15	2191.78	5864.37
	202504	1	5	2	8	855.94	2641.75	1274.36	4772.05
	202505	5	2	null	7	2231.2	458.68	null	2689.88
	All months	14	15	9	38	6647.17	7121.1	4841.44	18609.71
All countries	202501	5	4	11	20	2693.58	1845.01	6275.11	10813.7
	202502	10	9	7	26	4787.66	5208.46	2659.25	12655.37
	202503	7	13	12	32	4714.98	6596.16	7336.14	18647.28
	202504	7	10	4	21	4171.99	4990.68	2316.78	11479.45
	202505	5	6	1	12	2231.2	3278.37	725.8	6235.37
	All months	34	42	35	111	18599.41	21918.68	19313.08	59831.17