rekalogika/pivot-table

A PHP library for transforming multidimensional data into a pivot table.

Full documentation is available at rekalogika.dev/pivot-table.

Features

• Handles complex data. Renders complex multi-dimensional data involving multiple dimensions and measures into equally complex pivot tables.
• Flexible pivoting: Configure which dimensions should be pivoted (in columns) vs unpivoted (in rows).
• Measures are dimension: Measures are regarded as a special dimension @values that can be flexibly positioned in the pivot structure.
• Subtotals: Renders subtotals if they exist in the source data.
• Dimension ordering: Honors the ordering of dimensions as specified in the ORDER BY clause of the SQL query.
• Accepts simple array data: Compatible with all result set formats. Does not require specific database and database access libraries.
• Custom integration options: Options for deeper integration with your application by providing your custom data structure in table-like data, or in data cube if your data is already in that format.
• Customizable legends: Define custom labels for dimensions, measures, and subtotals.
• Does not require a specific data type: Your dimension members and measure values can be of any type, including enums, entity objects, money-pattern objects, etc. You can supply a custom renderer to format the output as required.

Design Philosophy

The library only transforms the data into a pivot table, but does not perform any calculations like sum, average, etc, including for calculating subtotals. This decision is taken because some aggregation functions, like Hyperloglog, are database specific, and cannot be easily performed using PHP. Instead, it expects the data to be already cubed.

If your data needs to be manually cubed, then it should be possible to use a cubing layer, but it is not yet written.

Requirements

PHP 8.2 or higher.

Technically, the library does not require a specific database software, it does not even require a database at all. However, it works best if the data is properly cubed. If you are getting the data from a database, the most convenient way to accomplish that is to use a database server that supports the GROUP BY CUBE() clause, like PostgreSQL and SQL Server.

Installation

Install via Composer:

composer require rekalogika/pivot-table

Basic Usage

First, we need to procure the data. For maximum functionality, the result should be properly 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

Related Packages

If you are using Doctrine ORM, you might be interested in rekalogika/doctrine-advanced-group-by. The package allows you to use CUBE(), GROUPING() and other grouping sets functions in DQL queries and QueryBuilder.

The package rekalogika/analytics is a full-fledged analytical solution for Doctrine ORM. This package originated as a component of that package, but was later generalized to be useful in other use-cases.

Documentation

For detailed documentation, examples, and advanced usage patterns, visit rekalogika.dev/pivot-table.

License

MIT

Contributing

Issues and pull requests should be filed in the GitHub repository rekalogika/pivot-table.