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.
The code is available: https://github.com/manticore-projects/MJdbcUtils 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/0 1/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 a nd 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 Co sts01/01/2023-10,000.00USD 02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Co sts31/01/20232,076.97USD 02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Co sts28/02/20231,903.10USD 02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Co sts31/03/20232,067.89USD 02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Co sts30/04/20231,962.77USD 02.02.11.000001Insurance Fees Expense02.11.000001Prepaid Acquisition Co sts31/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.67US D 02.02.11.000002Risk Margin02.11.000002Expected Claims28/02/202379.46USD 02.02.11.000002Risk Margin02.11.000002Expected Claims31/03/2023224.28US D 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/20234 0,000.00USD 02.02.11.000003Expected Claims02.11.000003Risk Margin01/01/2023438.90US D 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/20 2317,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/20 23-17,000.00USD 06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expe nse01/01/202310,000.00USD 06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expe nse31/01/2023-2,076.97USD 06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expe nse28/02/2023-1,903.10USD 06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expe nse31/03/2023-2,067.89USD 06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expe nse30/04/2023-1,962.77USD 06.06.04.110001Prepaid Acquisition Costs06.04.110001Insurance Fees Expe nse31/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/0 1/2023-10,000.00USD 06.06.11.000001Contractual Service Margin06.11.000001Premium Income Ins urance31/01/20232,267.71USD 06.06.11.000001Contractual Service Margin06.11.000001Premium Income Ins urance28/02/20232,923.41USD 06.06.11.000001Contractual Service Margin06.11.000001Premium Income Ins urance31/03/20231,328.32USD 06.06.11.000001Contractual Service Margin06.11.000001Premium Income Ins urance30/04/20231,599.61USD 06.06.11.000001Contractual Service Margin06.11.000001Premium Income Ins urance31/05/20234,880.95USD 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu rance01/01/2023-10,000.00USD 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu rance31/01/20231,916.61USD 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu rance28/02/20231,825.65USD 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu rance31/03/20232,063.57USD 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu rance30/04/20232,040.25USD 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu rance31/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 AccountUSD1 0,000.0010,000.00 01.0x.0x.999999Pending Claims0x.0x.999999Settlement AccountUSD17,000.00 17,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 a nd 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 Co stsUSD-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.0 040,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 MarginUSD 13,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,00 0.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 Expe nseUSD10,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 Ins uranceUSD2,267.712,923.411,328.321,599.614,880.9513,000.00 06.06.11.000001Deferred Acquisition Cost06.11.000001Premium Income Insu ranceUSD-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 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8fe9a16127cb19bf81813aa9c18bbffc72353acc.camel%40manticore-projects.com.
