I am working with an Innodb table in long format (training) and need to pivot this to wide format (pivTraining) reporting the latest (MAX???) date for each program.
I am using the following statement on Maria 10.5
CREATE TABLE pivTraining
ENGINE=connect TABLE_TYPE=pivot
OPTION_LIST='PIVOTCOL=programID,FNCCOL=trainingDate,FUNCTION=MAX,USER=root,PASSWORD=######'
SRCDEF='SELECT userName, CONCAT('Program_',programID) AS programID, MAX(trainingDate) AS trainingDate FROM training GROUP BY userName,programID';
and am trying to go from this:
MariaDB [db]> select * from training; +-----+----------+-----------+--------------+----------------+
| tid | userName | programID | trainingDate | trainingExpiry |
+-----+----------+-----------+--------------+----------------+
| 1 | kennjoh4 | 1 | 2023-12-17 | 365 |
| 2 | kennjoh4 | 1 | 2023-12-19 | 365 |
| 3 | kennjoh3 | 1 | 2023-12-19 | 365 |
| 4 | kennjoh4 | 1 | 2023-12-19 | 365 |
| 6 | kennjoh3 | 1 | 2023-12-21 | 365 |
| 7 | kennjoh3 | 1 | 2023-12-21 | 365 |
| 10 | kennjoh3 | 2 | 2022-12-05 | 365 |
| 11 | kennjoh | 1 | 2023-10-04 | 365 |
+-----+----------+-----------+--------------+----------------+
to this:
MariaDB [db]> select * from pivTraining; +----------+------------+------------+
| userName | Program_1 | Program_2 |
+----------+------------+------------+
| kennjoh | 2023-10-04 | NULL |
| kennjoh3 | 2023-12-21 | 2022-12-05 |
| kennjoh4 | 2023-12-19 | NULL |
+----------+------------+------------+
However what I'm getting is:
MariaDB [db]> select * from pivTraining; +----------+------------+------------+
| userName | Program_1 | Program_2 |
+----------+------------+------------+
| kennjoh | 1970-01-01 | 1970-01-01 |
| kennjoh3 | 1970-01-01 | 1970-01-01 |
| kennjoh4 | 1970-01-01 | 1970-01-01 |
+----------+------------+------------+
This looks to me like the connect engine is seeing the date type but doing something to it that results in a value of 0 hence the 1970-01-01
I have tried:
- removing the MAX function in the SRCDEF clause i.e. MAX(trainingDate) as trainingDate and removing the FUNCTION=MAX from the OPTIONS_LIST. I think this MAX is probably necessary though due otherwise the intermediate table gives the first value of trainingDate.
- adding DATE_FORMAT=YYYY-MM-DD to the options list
- reading the docs https://mariadb.com/kb/en/connect-pivot-table-type/#defining-a-pivot-table-from-a-source-table
Does anyone with experience of the connect engine know what may be going on here? I'm assuming it is something to do with the aggregating function.
outputs of describe:
MariaDB [db]> describe training;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| tid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| userName | varchar(255) | NO | | NULL | |
| programID | int(10) unsigned | NO | | NULL | |
| trainingDate | date | YES | | NULL | |
| trainingExpiry | int(10) unsigned | NO | | 365 | |
+----------------+------------------+------+-----+---------+----------------+
MariaDB [db]> describe pivTraining;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| userName | varchar(255) | NO | | NULL | |
| 1 | date | YES | | NULL | |
| 2 | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
Intermediate Table
MariaDB [db]> SELECT userName,
CONCAT('Program_',programID) AS program,
MAX(trainingDate) AS trainingDate,
MAX(ADDDATE(trainingDate, INTERVAL trainingExpiry DAY)) AS expiryDate
FROM training
GROUP BY userName, programID;
+----------+-----------+--------------+------------+
| userName | program | trainingDate | expiryDate |
+----------+-----------+--------------+------------+
| kennjoh | Program_1 | 2023-10-04 | 2024-10-03 |
| kennjoh3 | Program_1 | 2023-12-21 | 2024-12-20 |
| kennjoh3 | Program_2 | 2022-12-05 | 2023-12-05 |
| kennjoh4 | Program_1 | 2023-12-19 | 2024-12-18 |
+----------+-----------+--------------+------------+