MySQL dynamic pivot table [duplicate]

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: sqlfiddle.com/#!9/0ef81/2 and accept the duplicate :) — jpw
– jpw, Commented Aug 19, 2015 at 15:02

Rico Humme · Accepted Answer · 2015-08-19 15:13:36Z

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, 2015 at 15:13

Rico Humme

4785 silver badges12 bronze badges

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

1 Comment

panofish Over a year ago

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?

Rick James · Accepted Answer · 2015-08-19 18:22:19Z

0

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

answered Aug 19, 2015 at 18:22

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MySQL dynamic pivot table [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related