How to change rows to columns in MySQL

Question

I have a query that is displaying some information from various tables in a database.

SELECT    
                c.Name,
                jp.PieceType,
                jp.Qty
        FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.ID

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

Each company can have many piece types but right now the query only displays one type even though more exist. In the sqlfiddle database you can see company 123 has 3 PieceTypes. enter image description here

So I want to display the piece types as columns instead of rows. I want the PieceTypes to be displayed in separate columns like this: enter image description here

score 4 · Accepted Answer · answered Jul 10 '15 at 11:13

Hey try this for pivoting of table: SQLFIDDLE

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when PieceType = ''',
      PieceType,
      ''' then Qty end) AS ',
      concat(PieceType)
    )
  ) into @sql
from customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123;

set @sql = concat('select c.Name,', @sql, ' FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.ID
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

yes that works thanks. But why are the LEFT JOIN lines there twice? — user123456789, Jul 10 '15 at 11:19
Actually your data is conditional for group therefore this joins is used there — Vishal JAIN, Jul 10 '15 at 11:29

score 0 · Answer 2 · answered Jul 10 '15 at 10:45

You can use group_concat():

SELECT c.Name, group_concat(jp.PieceType) as piecetypes,
        group_concat(jp.Qty) as qtys
FROM customer c LEFT JOIN
     job_new jn
     ON c.JobID = jn.ID LEFT JOIN
     job_pieces jp
     ON c.JobID = jp.JobID
WHERE c.Company_ID = 123
GROUP BY c.ID;

Your original query has a major no-no. You have columns in the SELECT that are not in the GROUP BY. MySQL extends GROUP BY to allow this behavior. But, basically, you should never use it. Maybe there is some good use when you really know what you are doing, but no other databases support this extension. And, MySQL is making the GROUP BY ANSI-compliant in version 5.7.5.

thanks for the answer but I would like the PieceTypes to be in separate columns, not all together in one. I have edited my question to make it more clear what I am trying to do — user123456789, Jul 10 '15 at 10:48

How to change rows to columns in MySQL

2 Answers2

Linked