Question 365573 in Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I created the following procedure to create a view at run time: the result will be in the view:

    DELIMITER $$

CREATE PROCEDURE create_dynamic_view()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(255);
    DECLARE col_operations VARCHAR(255);
    DECLARE view_sql VARCHAR(4000);
    DECLARE cur CURSOR FOR SELECT name, operations FROM Table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Drop the view if it already exists
    DROP VIEW IF EXISTS dynamic_view;
    
    -- Start building the SQL for the dynamic view
    SET view_sql = 'CREATE VIEW dynamic_view AS SELECT date';
    
    -- Iterate through Table1 to generate columns dynamically
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name, col_operations;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Split the operations string into individual operations
        SET @operation_pos = 1;
        WHILE @operation_pos <= CHAR_LENGTH(col_operations) DO
            SET @comma_pos = LOCATE(',', col_operations, @operation_pos);
            IF @comma_pos = 0 THEN
                SET @operation = SUBSTRING(col_operations, @operation_pos);
                SET @operation_pos = CHAR_LENGTH(col_operations) + 1;
            ELSE
                SET @operation = SUBSTRING(col_operations, @operation_pos, @comma_pos - @operation_pos);
                SET @operation_pos = @comma_pos + 1;
            END IF;
            
            -- Add the dynamically generated column to the SQL
            SET view_sql = CONCAT(view_sql, ',');
            CASE 
                WHEN @operation = 'min' THEN
                    SET view_sql = CONCAT(view_sql, 'MIN(CASE WHEN name = ''', col_name, ''' THEN value END) AS ', col_name, '_min');
                WHEN @operation = 'max' THEN
                    SET view_sql = CONCAT(view_sql, 'MAX(CASE WHEN name = ''', col_name, ''' THEN value END) AS ', col_name, '_max');
                WHEN @operation = 'avg' THEN
                    SET view_sql = CONCAT(view_sql, 'AVG(CASE WHEN name = ''', col_name, ''' THEN value END) AS ', col_name, '_avg');
            END CASE;
        END WHILE;
    END LOOP;
    CLOSE cur;
    
    -- Finish building the SQL and execute it to create the dynamic view
    SET @final_sql = CONCAT(view_sql, ' FROM Table2 INNER JOIN Table1 ON Table1.id = Table2.table1_id GROUP BY date');
    PREPARE stmt FROM @final_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
END$$

DELIMITER ;

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search