MySQL dynamic INSERT INTO denormalized table from another table

Question

Let's say I have this table A with 300k rows

+---------+---------+---------+--------+--------
|   id    | Col1    |  Col2   |  Col 3 | Col n..
+---------+---------+---------+--------+--------
| id val  | Value 1 | Value 2 |  Val 3 | n..
+---------+---------+---------+--------+--------

I want to create a denormalized table B that would look like

+---------+---------+---------+
|tab_a_id |  Key    |  Val    |  
+---------+---------+---------+
| id val  | Col 1   | Value 1 |  
+---------+---------+---------+
| id val  | Col 2   | Value 2 |  
+---------+---------+---------+
| id val  | Col 3   | Val 3   |  
+---------+---------+---------+
| id val  | Col n.. | n..     |

Is there a way to dynamically run over the columns of table A to insert it into table B with the id from Table A, the column name as Key and the Value of each column?

possible duplicate of [MYSQL - Rows to Columns](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — void, Jan 08 '15 at 03:37

peterm · Accepted Answer · 2015-01-08T04:39:00.893

One way to unpivot TableA into TableB

INSERT INTO tableb (tab_a_id, `key`, val)
SELECT a.id, 
       CASE n.n 
          WHEN 1 THEN 'Col1'
          WHEN 2 THEN 'Col2'
          WHEN 3 THEN 'Col3'
          ...
       END,
       CASE n.n 
          WHEN 1 THEN Col1
          WHEN 2 THEN Col2
          WHEN 3 THEN Col3
          ...
       END
  FROM tablea a CROSS JOIN
(
  SELECT 1 n UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 
  ...
) n
 ORDER BY a.id, n.n;

Outcome:

| TAB_A_ID |  KEY |     VAL |
|----------|------|---------|
|   id val | Col1 | Value 1 |
|   id val | Col2 | Value 2 |
|   id val | Col3 | Value 3 |

Here is a SQLFiddle demo

Now if you want to make it totally dynamic you can leverage dynamic sql and wrap it up into stored procedure for convenience

DELIMITER //
CREATE PROCEDURE unpivot()
BEGIN
  SET @sql = NULL, @c = NULL, @v = NULL, @n = NULL;

  SELECT GROUP_CONCAT(CONCAT('WHEN ', ordinal_position, ' THEN ''', column_name, '''') SEPARATOR ' '),
         GROUP_CONCAT(CONCAT('WHEN ', ordinal_position, ' THEN `', column_name, '`') SEPARATOR ' '),
         GROUP_CONCAT(CONCAT('SELECT ', ordinal_position, ' n') SEPARATOR ' UNION ALL ')
    INTO @c, @v, @n
    FROM INFORMATION_SCHEMA.columns
   WHERE table_schema = SCHEMA()
     AND table_name = 'TableA'
     AND column_name NOT IN ('id');

  SET @sql = CONCAT(
    'INSERT INTO tableb (tab_a_id, `key`, val) ',
    'SELECT a.id, CASE n.n ', @c, ' END, CASE n.n ', @v, ' END ',
    'FROM tablea a CROSS JOIN (', @n, ') n ORDER BY a.id, n.n'
  );

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

Use it:

CALL unpivot();

Here is a SQLFiddle demo

MySQL dynamic INSERT INTO denormalized table from another table

1 Answers1