Dynamic SQL code to convert rows to columns

Question

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.

Isn't it more feasible to write a php (or whatever language) script that does this (you said transform and I assume this is permanent then) ? Run it once and you are done. — Cosmin
– Cosmin, Commented Sep 9, 2015 at 5:47
When I say transform I mean to create a query to get the data in different way. — dimoss
– dimoss, Commented Sep 9, 2015 at 5:53
Not exactly what you are looking for but something like GROUP_CONCAT might be a starting point — Orangepill
– Orangepill, Commented Sep 9, 2015 at 5:54

Devart · Accepted Answer · 2015-09-09 07:10:27Z

1

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)

edited Sep 9, 2015 at 7:10

answered Sep 9, 2015 at 6:34

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dimoss Over a year ago

Thanks Devart :-) Your sql script did the trick. Now the only thing is to create the column with the 'ipiresia' number in the actual table.

Cosmin · Accepted Answer · 2015-09-09 05:58:45Z

0

Like Orangepill suggested, group concat is the answer here, but you will have the ipiresia result in one column :

SELECT am, GROUP_CONCAT(ipiresia)
FROM table
GROUP BY am

You should have something like

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

answered Sep 9, 2015 at 5:58

Cosmin

1,49012 silver badges26 bronze badges

1 Comment

dimoss Over a year ago

Yes, but I want them in different columns. Unfortunately this does not cover my needs.

Collectives™ on Stack Overflow

Dynamic SQL code to convert rows to columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related