dimoss dimoss - 1 month ago 17
MySQL Question

Dynamic SQL code to convert rows to columns

I have a table in MySQL like this:

+-------+----------+
| am | ipiresia |
+-------+----------+
| 50470 | 29 |
| 50470 | 43 |
| 50433 | 29 |
| 6417 | 51 |
| 6417 | 52 |
| 6417 | 53 |
| 4960 | 25 |
| 4960 | 26 |
| 5567 | 89 |
| 6716 | 88 |
+-------+----------+


I want to transform it like this:

+-------+-----------+-----------+-----------+
| am | ipiresia1 | ipiresia2 | ipiresia3 |
+-------+-----------+-----------+-----------+
| 50470 | 29 | 43 | |
| 50433 | 29 | | |
| 6417 | 51 | 52 | 53 |
| 4960 | 25 | 26 | |
| 5567 | 89 | | |
| 6716 | 88 | | |
+-------+-----------+-----------+-----------+


Of course this is only a part of the table. The max occurrences of 'ipiresia' per 'am' can be up to 5, so I think a dynamic pivot table could do the work but I don't know how to do it.

Answer

Firstly - add one more column with an exact ipiresia number -

+-------+----------+-----+
|  am   | ipiresia | num |
+-------+----------+-----+
| 50470 |       29 |   1 |
| 50470 |       43 |   2 |
| 50433 |       29 |   1 |
|  6417 |       51 |   1 |
|  6417 |       52 |   2 |
|  6417 |       53 |   3 |
|  4960 |       25 |   1 |
|  4960 |       26 |   2 |
|  5567 |       89 |   1 |
|  6716 |       88 |   1 |
+-------+----------+-----+

Then use dynamic pivot -

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(num = ''',
      num,
      ''', ipiresia, NULL)) AS ',
      CONCAT('ipiresia', num)
    )
  ) INTO @sql
FROM ipiresia;
SET @sql = CONCAT('SELECT am, ', @sql, ' FROM ipiresia GROUP BY am');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Result -

+-------+-----------+-----------+-----------+
| am    | ipiresia1 | ipiresia2 | ipiresia3 |
+-------+-----------+-----------+-----------+
|  4960 |        25 |        26 |      NULL |
|  5567 |        89 |      NULL |      NULL |
|  6417 |        51 |        52 |        53 |
|  6716 |        88 |      NULL |      NULL |
| 50433 |        29 |      NULL |      NULL |
| 50470 |        29 |        43 |      NULL |
+-------+-----------+-----------+-----------+

Automate pivot table queries

Dynamic pivot tables (transform rows to columns)

Comments