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.
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.