MySQL dynamic pivot table -

this question has answer here:

• mysql dynamic pivot table 2 answers

i have simplified data following:

+---------+-----+--------+ | item  | type  | color  | +-------+-------+--------+ |   1   |     | red    | |   1   |   b   | blue   | |   2   |     | orange | |   2   |   b   | pink   | |   2   |   c   | blue   | |   3   |   b   | yellow | +---------+-----+--------+ 

the number of 'type' per item variable, therefore, need mysql pivot solution dynamic , can handle number of types. following kind of result set need return mysql query. can see, not need summary calculation.

+---------+------+--------+--------+ | item  |      |   b    |   c    | +-------+--------+--------+--------+ |   1   | red    | blue   |        |  |   2   | orange | pink   | blue   | |   3   |        | yellow |        | +-------+--------+--------+--------+ 

i suspect solution may involve use of mysql procedure?

select item, min(a), min(b), min(c) (     select item,          case when type = 'a' color end 'a',         case when type = 'b' color end 'b',         case when type = 'c' color end 'c'     color ) tbl group item