sql - Mysql query to dynamically convert rows to columns -

sql - Mysql query to dynamically convert rows to columns -

can mysql convert columns rows, dynamically adding many columns needed rows. think question might related pivot tables i'm unsure , don't know how frame question other giving next example.

given 2 tables , b, like

table a

+--+-----+----+ |id|order|data| +--+-----+----+ |1 |1 |p | +--+-----+----+ |2 |2 |q | +--+-----+----+ |2 |1 |r | +--+-----+----+ |1 |2 |s | +--+-----+----+

i write query looks following:

result table

+--+-----+-----+ |id|data1|data2| +--+-----+-----+ |1 |p |s | +--+-----+-----+ |2 |r |q | +--+-----+-----+

basically want turn each row in table b column in result table. if there new entry added table b id=1, want result table automatically extend 1 column accommodate info point.

you can utilize group by , max simulate pivot. mysql supports if statement.

select id, max(if(`order` = 1, data, null)) data1, max(if(`order` = 2, data, null)) data2 tablea grouping id sqlfiddle demo

if have multiple values of order, dynamic sql more appropriate not have modify query,

set @sql = null; select group_concat(distinct concat( 'max(if(`order` = ', `order`, ',data,null)) data', `order`) ) @sql tablename; set @sql = concat('select id, ', @sql, ' tablename grouping id'); prepare stmt @sql; execute stmt; deallocate prepare stmt; sqlfiddle demo sqlfiddle demo (another example)

output of both queries:

╔════╦═══════╦═══════╗ ║ id ║ data1 ║ data2 ║ ╠════╬═══════╬═══════╣ ║ 1 ║ p ║ s ║ ║ 2 ║ r ║ q ║ ╚════╩═══════╩═══════╝

mysql sql pivot