rekalogika / pivot-table
Transforms multidimensional data into a pivot table.
Fund package maintenance!
priyadi
Installs: 1 737
Dependents: 1
Suggesters: 1
Security: 0
Stars: 0
Watchers: 2
Forks: 0
Open Issues: 1
Requires
- php: ^8.2
Requires (Dev)
- ekino/phpstan-banned-code: ^3.0
- phpat/phpat: ^0.11.9
- phpstan/phpstan: ^2.1
- phpstan/phpstan-deprecation-rules: ^2.0
- phpstan/phpstan-phpunit: ^2.0
- phpunit/phpunit: ^11.0
- psalm/plugin-phpunit: ^0.19.5
- psalm/plugin-symfony: ^5.2
- rector/rector: ^2.1
- vimeo/psalm: ^6.13
- dev-main
- v0.32.0
- v0.31.0
- v0.30.0
- v0.29.0
- v0.28.0
- v0.27.0
- v0.26.1
- v0.26.0
- v0.25.0
- v0.24.1
- v0.24.0
- v0.23.2
- v0.23.1
- v0.23.0
- v0.22.1
- v0.22.0
- v0.21.5
- v0.21.4
- v0.21.3
- v0.21.2
- v0.21.1
- v0.21.0
- v0.20.6
- v0.20.4
- v0.20.3
- v0.20.2
- v0.20.1
- v0.10.1
- v0.10.0
- dev-ci/8.5
- dev-refactor/renames
- dev-docs/readme
- dev-feat/no-grouping
- dev-feat/rollup
- dev-chore/namespace
This package is auto-updated.
Last update: 2025-08-25 18:22:51 UTC
README
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
.