Adding a pivot table to my SQL select query

I am trying to display information from different tables in a database. I created a sample database here http://www.sqlfiddle.com/#!9/c34306/3.
Here is the SQL query:

SELECT c.Name,
       GROUP_CONCAT(DISTINCT jp.PieceType) as PieceType
FROM customer c
LEFT JOIN job_new jn ON c.Company_ID = jn.CompanyID
LEFT JOIN job_pieces jp ON c.Company_ID = jp.CompanyID
WHERE c.Company_ID = 123

The PieceType column is displaying the types all in one column, but I would like them to be displayed in separate columns. So each piece type would in in its own column. Each company could have a different number of PieceTypes.

I have tried creating a pivot table so solve this problem but the SQL is not displaying any information. Just an SQL message Record Count: 0; Execution Time: 1m.

SET group_concat_max_len=5000;
SET @COLUMNS = NULL;

/* Build columns to pivot */
SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'GROUP_CONCAT(IF(jp.PieceType = "',
            jp.PieceType ,
            '", 1, NULL)) AS ',
            jp.PieceType
        )
) INTO @COLUMNS
FROM job_pieces jp;

/* Build full query */
SET @SQL = CONCAT(
        'SELECT  
                c.Name,
                ',@COLUMNS,'
        FROM customer c
        LEFT JOIN job_new jn ON c.Company_ID = jn.CompanyID
        LEFT JOIN job_pieces jp ON c.Company_ID = jp.CompanyID
        WHERE c.Company_ID = 123
        GROUP BY c.ID'
);

I have also added this code to sqlfiddle: http://www.sqlfiddle.com/#!9/c34306/12


Source: sql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.