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