mysqldynamic-pivot

MySQL, how to solve this problem using dynamic pivot


I want to create a dynamic_view using a stored procedure for the following scenario.

Table1 has three columns (id, name, operations), and it has the following three records.

id|name|operations
------------------
1 | a  |min,max
2 | b  |min,max,avg
3 | c  |avg

Table2 has four columns (id, date, table1_id, value) that stores values for table1 records as following:

id | date    |table1_id | value
------------------------------- 
1  | 2024-1-1| 1        | 2.1
2  | 2024-1-1| 1        | 2.2
3  | 2024-1-1| 2        | 2.3
4  | 2024-1-1| 2        | 2.4
5  | 2024-1-1| 3        | 2.5
6  | 2024-1-1| 3        | 2.6
7  | 2024-1-2| 1        | 3.1
8  | 2024-1-2| 1        | 3.2
9  | 2024-1-2| 2        | 3.3
10 | 2024-1-2| 2        | 3.4
11 | 2024-1-2| 3        | 3.5
12 | 2024-1-2| 3        | 3.6

Now, I want a stored procedure to create a view dynamically that has eight columns (id, date, amin, amax, bmin, bmax, bavg, cavg) based on table1 records for columns and operations and their records should be based on table2 data. this id should be auto-incremental. like the following

id | date    |amin|amax|bmin|bmax|bavg|cavg
-------------------------------------------
1  | 2024-1-1|2.1 |2.2 | 2.3|2.4 |2.35|2.55
2  | 2024-1-2|3.1 |3.2 | 3.3|3.4 |3.35|3.55 

Your support is highly appreciated it.


Solution

  • No pivot table can make "dynamic columns." Columns are determined by your select-list, and they are fixed before the query begins querying data.

    You could run two queries, one to read table1 and generate the expressions for a select-list:

    SELECT CONCAT(ops.op, '(value) as ', table1.name, ops.op) AS expr
    FROM table1
    INNER JOIN (
      VALUES ROW('min'), ROW('max'), ROW('avg')
    ) AS ops(op) ON FIND_IN_SET(ops.op, table1.operations);
    

    The result given your example data:

    +--------------------+
    | expr               |
    +--------------------+
    | min(value) as bmin |
    | min(value) as amin |
    | max(value) as bmax |
    | max(value) as amax |
    | avg(value) as cavg |
    | avg(value) as bavg |
    +--------------------+
    

    Then you must write application code to fetch that result set, and use it to format the select-list of a second query:

    SELECT ROW_NUMBER() OVER () AS `id`,
      `date`,
      min(value) as bmin,
      min(value) as amin,
      max(value) as bmax,
      max(value) as amax,
      avg(value) as cavg,
      avg(value) as bavg
    FROM table2
    GROUP BY `date`;
    

    I tested this and here's the result given your data:

    +----+------------+------+------+------+------+---------+---------+
    | id | date       | bmin | amin | bmax | amax | cavg    | bavg    |
    +----+------------+------+------+------+------+---------+---------+
    |  1 | 2024-01-01 |  2.1 |  2.1 |  2.6 |  2.6 | 2.35000 | 2.35000 |
    |  2 | 2024-01-02 |  3.1 |  3.1 |  3.6 |  3.6 | 3.35000 | 3.35000 |
    +----+------------+------+------+------+------+---------+---------+
    

    This must be two queries run separately. You can't even put the first query in as a subquery in the second query, because the subquery will return strings, not expressions.