sql - Mysql query to dynamically convert rows to columns on the basis of two columns -

i have followed question here use mysql query dynamically convert rows columns. works fine, need convert on basis of 2 columns,

the query mentioned in above link works single column "data", want work 2 columns "data" , "price".

i have added example here,

given table a, like

table  |  id|order|data|item|price| -----+-----+---------------- |   1|    1|   p| 1  | 50  | |   1|    1|   p| 2  | 60  | |   1|    1|   p| 3  | 70  | |   1|    2|   q| 1  | 50  | |   1|    2|   q| 2  | 60  | |   1|    2|   q| 3  | 70  | |   2|    1|   p| 1  | 50  | |   2|    1|   p| 2  | 60  | |   2|    1|   p| 4  | 80  | |   2|    3|   s| 1  | 50  | |   2|    3|   s| 2  | 60  | |   2|    3|   s| 4  | 80  | 

i write query looks following:

result table  |  id|order1|order2|order3|item1|item2|item3|item4| -----+-----+--------------------------------------- |   1|    p |    q |      | 50  | 60  | 70  |     | |   2|    p |      |    s | 50  | 60  |     | 80  | 

i have tried create 2 different queries , join achieve this, may not solution. can 1 suggest solution same mentioned in link above.

thanks

if had known number of values both order , item, hard code query into:

select id,   max(case when `order` = 1 data end) order1,   max(case when `order` = 2 data end) order2,   max(case when `order` = 3 data end) order3,   max(case when item = 1 price end) item1,   max(case when item = 2 price end) item2,   max(case when item = 3 price end) item3,   max(case when item = 4 price end) item4 tablea group id; 

see demo. part of problem going have because trying transform multiple columns of data. suggestion final result unpivot data first. mysql not have unpivot function can use union convert multiple pairs of columns rows. code unpivot similar following:

select id, concat('order', `order`) col,  data value tablea union select id, concat('item', item) col, price value tablea; 

see demo. result of be:

| id |    col | value | ----------------------- |  1 | order1 |     p | |  1 | order1 |     p | |  1 | order1 |     p | |  1 |  item1 |    50 | |  1 |  item2 |    60 | |  1 |  item3 |    70 | 

as can see has taken multiple columns of order/data , item/price , convert multiple rows. once completed, can convert values columns using aggregate function case:

select id,    max(case when col = 'order1' value end) order1,   max(case when col = 'order2' value end) order2,   max(case when col = 'order3' value end) order3,   max(case when col = 'item1' value end) item1,   max(case when col = 'item2' value end) item2,   max(case when col = 'item3' value end) item3 (   select id, concat('order', `order`) col,  data value   tablea   union   select id, concat('item', item) col, price value   tablea ) d group id; 

see demo. finally, need convert above code dynamic prepared statement query:

set @sql = null; select   group_concat(distinct     concat(       'max(case when col = ''',       col,       ''' value end) `',        col, '`')   ) @sql (   select concat('order', `order`) col   tablea   union   select concat('item', `item`) col   tablea )d;  set @sql = concat('select id, ', @sql, '                                      (                     select id, concat(''order'', `order`) col,  data value                     tablea                     union                     select id, concat(''item'', item) col, price value                     tablea                   ) d                   group id');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

see sql fiddle demo. gives result:

| id | order1 | order2 | order3 | item1 | item2 |  item3 |  item4 | ------------------------------------------------------------------- |  1 |      p |      q | (null) |    50 |    60 |     70 | (null) | |  2 |      p | (null) |      s |    50 |    60 | (null) |     80 |