sql - How to change rows to columns in MySQL -

this question has answer here:

• mysql - rows columns 10 answers

i have query displaying information various tables in database.

select                     c.name,                 jp.piecetype,                 jp.qty         customer c         left join job_new jn on c.jobid = jn.id         left join job_pieces jp on c.jobid = jp.jobid         c.company_id = 123         group c.id 

i created database on sqlfiddle: http://www.sqlfiddle.com/#!9/13230/8

each company can have many piece types right query displays 1 type though more exist. in sqlfiddle database can see company 123 has 3 piecetypes.

so want display piece types columns instead of rows. want piecetypes displayed in separate columns this:

hey try pivoting of table: sqlfiddle

set @sql = null; select   group_concat(distinct     concat(       'max(case when piecetype = ''',       piecetype,       ''' qty end) ',       concat(piecetype)     )   ) @sql customer c         left join job_new jn on c.jobid = jn.id         left join job_pieces jp on c.jobid = jp.jobid         c.company_id = 123;  set @sql = concat('select c.name,', @sql, ' customer c         left join job_new jn on c.jobid = jn.id         left join job_pieces jp on c.jobid = jp.jobid         c.company_id = 123         group c.id ');  prepare stmt @sql; execute stmt; deallocate prepare stmt;