Building Pivot Tables with Example

115 views
Skip to first unread message

Andreas Reichel

unread,
Dec 12, 2023, 9:47:02 AM12/12/23
to h2-database
Greetings!

Since H2 does not support the `PIVOT` clause yet, we have had to develop a work around, which we would like to share.
Our solution is RDBMS agnostic and uses a plain JDBC `ResultSet` with a Category Column for pivoting and a Values Column for aggregation. It will return an Object[][] array suitable to fill a JTable or a spread sheet.

Please see below an accounting driven example for deriving the Comprehensive Income View from a Table of Debit/Credit entries below.

Restrictions:
- currently only the SUM aggregate is supported, we plan to amend this eventually
- currently only BigDecimal values are supported, we are going to amend this for any other scalar data type

Plans:
- write a CREATE TABLE statement based on the data
- write a CREATE MATERIALISED VIEW statement

Please do let me know what you think of it and which additional features may be useful. Your PRs and suggestions will be most welcome and appreciated.

Thank you for your time and cheers
Andreas


0) Maven Artifact

implementation('com.manticore-projects.jdbc:MJdbcUtils:+') {changing=true}

1) Source ResultSet, with VALUE_DATE as category and AMOUNT as agregate data (78 records in total, to be aggregated over 5 months)

GL_LINECODE_FROMDESCRIPTION_FROMCODE_TODESCRIPTION_TOVALUE_DATEAMOUNTID_CURRENCY
01.0x.0x.999999Expected Claims0x.0x.999999Settlement Account01/01/2023-40,000.00USD
01.0x.0x.999999Insurance Fees Expense0x.0x.999999Settlement Account01/01/202310,000.00USD
01.0x.0x.999999Pending Claims0x.0x.999999Settlement Account31/03/202317,000.00USD
02.02.06.000001Insurance Fees Expense02.06.000001Previous year Profit and Loss31/12/2023-27,000.00USD
02.02.06.000001Premium Income Insurance02.06.000001Previous year Profit and Loss31/12/202340,000.00USD
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Costs01/01/2023-10,000.00USD
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Costs31/01/20232,076.97USD
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Costs28/02/20231,903.10USD
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Costs31/03/20232,067.89USD
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Costs30/04/20231,962.77USD
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Costs31/05/20231,989.27USD
02.02.11.000002Contractual Service Margin02.11.000002Expected Claims01/01/2023-13,556.09USD
02.02.11.000002Contractual Service Margin02.11.000002Expected Claims31/01/2023-3,270.02USD
02.02.11.000002Contractual Service Margin02.11.000002Expected Claims28/02/2023-3,270.62USD
02.02.11.000002Contractual Service Margin02.11.000002Expected Claims31/03/202313,577.29USD
02.02.11.000002Contractual Service Margin02.11.000002Expected Claims30/04/2023-3,219.17USD
02.02.11.000002Contractual Service Margin02.11.000002Expected Claims31/05/2023-3,261.39USD
02.02.11.000002Insurance Loss02.11.000002Expected Claims31/03/20233,310.78USD
02.02.11.000002Insurance Loss02.11.000002Expected Claims31/05/2023-3,310.78USD
02.02.11.000002Premium Income Insurance02.11.000002Expected Claims01/01/2023-10,000.00USD
02.02.11.000002Premium Income Insurance02.11.000002Expected Claims31/03/2023-17,000.00USD
02.02.11.000002Risk Margin02.11.000002Expected Claims01/01/2023-438.90USD
02.02.11.000002Risk Margin02.11.000002Expected Claims31/01/2023110.67USD
02.02.11.000002Risk Margin02.11.000002Expected Claims28/02/202379.46USD
02.02.11.000002Risk Margin02.11.000002Expected Claims31/03/2023224.28USD
02.02.11.000002Risk Margin02.11.000002Expected Claims30/04/20230.42USD
02.02.11.000002Risk Margin02.11.000002Expected Claims31/05/202324.07USD
02.02.11.000002Settlement Account02.11.000002Expected Claims01/01/202340,000.00USD
02.02.11.000003Expected Claims02.11.000003Risk Margin01/01/2023438.90USD
02.02.11.000003Expected Claims02.11.000003Risk Margin31/01/2023-110.67USD
02.02.11.000003Expected Claims02.11.000003Risk Margin28/02/2023-79.46USD
02.02.11.000003Expected Claims02.11.000003Risk Margin31/03/2023-224.28USD
02.02.11.000003Expected Claims02.11.000003Risk Margin30/04/2023-0.42USD
02.02.11.000003Expected Claims02.11.000003Risk Margin31/05/2023-24.07USD
02.02.11.000004Expected Claims02.11.000004Contractual Service Margin01/01/202313,556.09USD
02.02.11.000004Expected Claims02.11.000004Contractual Service Margin31/01/20233,270.02USD
02.02.11.000004Expected Claims02.11.000004Contractual Service Margin28/02/20233,270.62USD
02.02.11.000004Expected Claims02.11.000004Contractual Service Margin31/03/2023-13,577.29USD
02.02.11.000004Expected Claims02.11.000004Contractual Service Margin30/04/20233,219.17USD
02.02.11.000004Expected Claims02.11.000004Contractual Service Margin31/05/20233,261.39USD
02.02.11.000004Premium Income Insurance02.11.000004Contractual Service Margin31/01/2023-2,267.71USD
02.02.11.000004Premium Income Insurance02.11.000004Contractual Service Margin28/02/2023-2,923.41USD
02.02.11.000004Premium Income Insurance02.11.000004Contractual Service Margin31/03/2023-1,328.32USD
02.02.11.000004Premium Income Insurance02.11.000004Contractual Service Margin30/04/2023-1,599.61USD
02.02.11.000004Premium Income Insurance02.11.000004Contractual Service Margin31/05/2023-4,880.95USD
02.02.11.000006Insurance Fees Expense02.11.000006Pending Claims31/03/202317,000.00USD
02.02.11.000006Settlement Account02.11.000006Pending Claims31/03/2023-17,000.00USD
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition Cost01/01/202310,000.00USD
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition Cost31/01/2023-1,916.61USD
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition Cost28/02/2023-1,825.65USD
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition Cost31/03/2023-2,063.57USD
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition Cost30/04/2023-2,040.25USD
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition Cost31/05/2023-2,153.92USD
06.06.04.110001Pending Claims06.04.110001Insurance Fees Expense31/03/2023-17,000.00USD
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expense01/01/202310,000.00USD
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expense31/01/2023-2,076.97USD
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expense28/02/2023-1,903.10USD
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expense31/03/2023-2,067.89USD
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expense30/04/2023-1,962.77USD
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expense31/05/2023-1,989.27USD
06.06.04.110001Previous year Profit and Loss06.04.110001Insurance Fees Expense31/12/202327,000.00USD
06.06.04.110001Settlement Account06.04.110001Insurance Fees Expense01/01/2023-10,000.00USD
06.06.11.000001Contractual Service Margin06.11.000001Premium Income Insurance31/01/20232,267.71USD
06.06.11.000001Contractual Service Margin06.11.000001Premium Income Insurance28/02/20232,923.41USD
06.06.11.000001Contractual Service Margin06.11.000001Premium Income Insurance31/03/20231,328.32USD
06.06.11.000001Contractual Service Margin06.11.000001Premium Income Insurance30/04/20231,599.61USD
06.06.11.000001Contractual Service Margin06.11.000001Premium Income Insurance31/05/20234,880.95USD
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insurance01/01/2023-10,000.00USD
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insurance31/01/20231,916.61USD
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insurance28/02/20231,825.65USD
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insurance31/03/20232,063.57USD
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insurance30/04/20232,040.25USD
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insurance31/05/20232,153.92USD
06.06.11.000001Expected Claims06.11.000001Premium Income Insurance01/01/202310,000.00USD
06.06.11.000001Expected Claims06.11.000001Premium Income Insurance31/03/202317,000.00USD
06.06.11.000001Previous year Profit and Loss06.11.000001Premium Income Insurance31/12/2023-40,000.00USD
06.06.11.100001Expected Claims06.11.100001Insurance Loss31/03/2023-3,310.78USD
06.06.11.100001Expected Claims06.11.100001Insurance Loss31/05/20233,310.78USD

2) transform into the Pivot

/**
 * The getPivotFromQuery function takes a ResultSet and converts it into a pivot table. The
 * function is designed to create columns for each key of the Category Column and to aggregate
 * the values of the Aggregate Column for each Category.
 *
 * @param rs The ResultSet holding the source data with the category values in rows
 * @param function Determine what type of aggregate function to use (SUM, COUNT, ...)
 * @param aggregateColumnName Specify the column name of the aggregate value
 * @param categoryColumnName Identify the column that will be transformed into separate Value
 *        Columns
 * @param categoryFormat Format the key values into column labels
 * @param buildTotals If to insert Total rows below and column on the right side
 * @param repeatHeader If to insert the header repeatedly before each category
 * @return A 2-dimensional array holding the transformed Column Names and the Data
 */

Object[][] data = MJdbcTools.getPivotFromQuery(
        resultSet, 
        MJdbcTools.AggregateFunction.SUM,
       "amount",
       "value_date",
        DateFormat.getDateInstance(DateFormat.SHORT, Locale.US),
       true,
       true);

3) resulting Pivot

GL_LINECODE_FROMDESCRIPTION_FROMCODE_TODESCRIPTION_TOID_CURRENCY1/1/231/31/232/28/233/31/234/30/235/31/2312/31/23Total
01.0x.0x.999999Expected Claims0x.0x.999999Settlement AccountUSD-40,000.00-40,000.00
01.0x.0x.999999Insurance Fees Expense0x.0x.999999Settlement AccountUSD10,000.0010,000.00
01.0x.0x.999999Pending Claims0x.0x.999999Settlement AccountUSD17,000.0017,000.00
01.Total-30,000.000.000.0017,000.000.000.000.00-13,000.00
02.02.06.000001Insurance Fees Expense02.06.000001Previous year Profit and LossUSD-27,000.00-27,000.00
02.02.06.000001Premium Income Insurance02.06.000001Previous year Profit and LossUSD40,000.0040,000.00
02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition CostsUSD-10,000.002,076.971,903.102,067.891,962.771,989.270.00
02.02.11.000002Contractual Service Margin02.11.000002Expected ClaimsUSD-13,556.09-3,270.02-3,270.6213,577.29-3,219.17-3,261.39-13,000.00
02.02.11.000002Insurance Loss02.11.000002Expected ClaimsUSD3,310.78-3,310.780.00
02.02.11.000002Premium Income Insurance02.11.000002Expected ClaimsUSD-10,000.00-17,000.00-27,000.00
02.02.11.000002Risk Margin02.11.000002Expected ClaimsUSD-438.90110.6779.46224.280.4224.070.00
02.02.11.000002Settlement Account02.11.000002Expected ClaimsUSD40,000.0040,000.00
02.02.11.000003Expected Claims02.11.000003Risk MarginUSD438.90-110.67-79.46-224.28-0.42-24.070.00
02.02.11.000004Expected Claims02.11.000004Contractual Service MarginUSD13,556.093,270.023,270.62-13,577.293,219.173,261.3913,000.00
02.02.11.000004Premium Income Insurance02.11.000004Contractual Service MarginUSD-2,267.71-2,923.41-1,328.32-1,599.61-4,880.95-13,000.00
02.02.11.000006Insurance Fees Expense02.11.000006Pending ClaimsUSD17,000.0017,000.00
02.02.11.000006Settlement Account02.11.000006Pending ClaimsUSD-17,000.00-17,000.00
02.02.11.000008Premium Income Insurance02.11.000008Deferred Acquisition CostUSD10,000.00-1,916.61-1,825.65-2,063.57-2,040.25-2,153.920.00
02.Total30,000.00-2,107.35-2,845.96-15,013.22-1,677.09-8,356.3813,000.0013,000.00
06.06.04.110001Pending Claims06.04.110001Insurance Fees ExpenseUSD-17,000.00-17,000.00
06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees ExpenseUSD10,000.00-2,076.97-1,903.10-2,067.89-1,962.77-1,989.270.00
06.06.04.110001Previous year Profit and Loss06.04.110001Insurance Fees ExpenseUSD27,000.0027,000.00
06.06.04.110001Settlement Account06.04.110001Insurance Fees ExpenseUSD-10,000.00-10,000.00
06.06.11.000001Contractual Service Margin06.11.000001Premium Income InsuranceUSD2,267.712,923.411,328.321,599.614,880.9513,000.00
06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income InsuranceUSD-10,000.001,916.611,825.652,063.572,040.252,153.920.00
06.06.11.000001Expected Claims06.11.000001Premium Income InsuranceUSD10,000.0017,000.0027,000.00
06.06.11.000001Previous year Profit and Loss06.11.000001Premium Income InsuranceUSD-40,000.00-40,000.00
06.06.11.100001Expected Claims06.11.100001Insurance LossUSD-3,310.783,310.780.00
06.Total0.002,107.352,845.96-1,986.781,677.098,356.38-13,000.000.00
Reply all
Reply to author
Forward
0 new messages