MySQL dynamic pivot table

Question

I have simplified my data to something much like the following:

+---------+-----+--------+
| item  | type  | color  |
+-------+-------+--------+
|   1   |   A   | red    |
|   1   |   B   | blue   |
|   2   |   A   | orange |
|   2   |   B   | pink   |
|   2   |   C   | blue   |
|   3   |   B   | yellow |
+---------+-----+--------+

The number of 'type' per item is variable, therefore, I need a MySQL pivot solution that is dynamic and can handle any number of types. The following is the kind of result set I need to return from the MySQL query. As you can see, I do not need a summary calculation.

+---------+------+--------+--------+
| item  |   A    |   B    |   C    |
+-------+--------+--------+--------+
|   1   | red    | blue   |        | 
|   2   | orange | pink   | blue   |
|   3   |        | yellow |        |
+-------+--------+--------+--------+

I suspect the solution may involve the use of a MySQL procedure?

See this: http://www.sqlfiddle.com/#!9/0ef81/2 and accept the duplicate :) — jpw, Aug 19 '15 at 15:02

score 0 · Answer 1 · answered Aug 19 '15 at 15:13

0

select item, min(A), min(B), min(C) from (
    select item, 
        case when type = 'A' then color end as 'A',
        case when type = 'B' then color end as 'B',
        case when type = 'C' then color end as 'C'
    from color
) as tbl
group by item

answered Aug 19 '15 at 15:13

Rico Humme

456
5
12

this works if the number of possible types is fixed, however the number of types may be fewer or greater. So I need the mysql statement to dynamically adapt to the data. This is why I suspect I may need a mysql procedure to build the case statements? – panofish Aug 19 '15 at 15:28

score 0 · Answer 2 · answered Aug 19 '15 at 18:22

0

You are "pivoting". In my blog, I provide a generic stored procedure to generate the SELECT statement for you.

answered Aug 19 '15 at 18:22

Rick James

135,179
13
127
222

MySQL dynamic pivot table

2 Answers2