How to set column name, use value dynamic in MariaDB

Question

I have 3 tables as follows:

tbl_reporting                               tbl_criteria                   tbl_student
|===============================|           |========================|     |===============|
| id | id_criteria | id_student |           | id |   name    |weight |     | id |   name   |
|===============================|           |========================|     |===============|
| 1  |     2       |      3     |           |  1 | worrying  |  3    |     | 1  | Nina     |
| 2  |     1       |      2     |           |  2 | naughty   |  2    |     | 2  | Adam     |
| 3  |     1       |      1     |           |  3 | usually   |  2    |     | 3  | Dodi     |
| 4  |     2       |      2     |           |  4 | good      |  1    |     | 4  | Zarah    |
| 5  |     1       |      1     |           |  5 | obey      |  1    |     | 5  | Udep     |

the actual result I want are as like follow, with count the weight on tbl_criteria


result
| student | worrying | naughty | usualy | good | obey | total |
|=============================================================|
| Nina    |    6     |   0     |    0   |   0  |  0   |   6   |
| Adam    |    3     |   2     |    0   |   0  |  0   |   5   |
| Dodi    |    0     |   2     |    0   |   0  |  0   |   2   |
| Zarah   |    0     |   0     |    0   |   0  |  0   |   0   |
| Udep    |    0     |   0     |    0   |   0  |  0   |   0   |

So, all values in tbl_criteria.name field will become with dynamic a new column , and count the weight.
I created a table and its inputs: http://sqlfiddle.com/#!9/41e637

v10.3.16-MariaDB

I found a similar article but I was still confused: https://stackoverflow.com/questions/27051989/mysql-dynamic-row-values-as-column-names — , Mar 12 '21 at 12:03
in line 9 error, when i try copy and insert table `... tablename)INTO @sql;` https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=18fb41d1722d17a7cc8f3ea837679ea0 — , Mar 12 '21 at 12:34
I do **NOT** see an error on your fiddle, I see normal execution. https://i.stack.imgur.com/FPH2S.png PS. **NEVER** use reserved words as column names. — Akina, Mar 12 '21 at 14:28
when i put it in my sql, it looks like too many quotes, https://imgur.com/a/GFKmd1u — , Mar 12 '21 at 14:45
but, which I find all are not dynamic values, is there any way to display it dynamically.? — , Mar 12 '21 at 15:06

score 0 · Accepted Answer · answered Mar 19 '21 at 09:34

pivot on mariadb

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Pivot`(
    IN tbl_name VARCHAR(99),       -- table name (or db.tbl)
    IN base_cols VARCHAR(99),      -- column(s) on the left, separated by commas
    IN pivot_col VARCHAR(64),      -- name of column to put across the top
    IN tally_col VARCHAR(64),      -- name of column to SUM up
    IN where_clause VARCHAR(99),   -- empty string or "WHERE ..."
    IN order_by VARCHAR(99)        -- empty string or "ORDER BY ..."; usually the base_cols
    )
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    -- Find the distinct values
    -- Build the SUM()s
    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
                    ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
    -- select @subq;

    SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
    SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
    SET @cc3 = REPLACE(@cc2, '&t', tally_col);
    -- select @cc2, @cc3;
    SET @qval = CONCAT("'\"', val, '\"'");
    -- select @qval;
    SET @cc4 = REPLACE(@cc3, '&v', @qval);
    -- select @cc4;

    SET SESSION group_concat_max_len = 10000;   -- just in case
    SET @stmt = CONCAT(
            'SELECT  GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n")  INTO @sums',
            ' FROM ( ', @subq, ' ) AS top');
     select @stmt;
    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                      -- Intermediate step: build SQL for columns
    DEALLOCATE PREPARE _sql;
    -- Construct the query and perform it
    SET @stmt2 = CONCAT(
            'SELECT ',
                base_cols, ',\n',
                @sums,
                ',\n SUM(', tally_col, ') AS Total'
            '\n FROM ', tbl_name, ' ',
            where_clause,
            ' GROUP BY ', base_cols,
            '\n WITH ROLLUP',
            '\n', order_by
        );
    select @stmt2;                    -- The statement that generates the result
    PREPARE _sql FROM @stmt2;
    EXECUTE _sql;                     -- The resulting pivot table ouput
    DEALLOCATE PREPARE _sql;
    -- For debugging / tweaking, SELECT the various @variables after CALLing.
END$$
DELIMITER ;

How to set column name, use value dynamic in MariaDB

1 Answers1