How to generate this SQL query like pivot table

Question

I want to three table union to one query like a pivot table. My main table name is products and two support tables name are code_keys and code_values

I have a main table name: products

+------------------+ 
| Field            |
+------------------+     
| productid        |     
| catpath          |  
| da               |
| l0               |
| ig               |
| des              |
| notes            |
| wire             |
| lmm              |
+------------------+

example data:

+-----------+------+------+------+
| productid | da   | ig   | des  |
+-----------+------+------+------+
|         5 | 78   | 33   | 23   |
|         8 | 88   | 13   | 21   |
+-----------+------+------+------+

and 2 conf table names: code_keys. Store code structure.

+--------+
| Field  |
+--------+
| codeid |
| title  |
| codekey|
| format |
+--------+

example data:

+--------+----------------+
| codeid | title          |
+--------+----------------+
|      2 | St Code        |
|      3 | Rear Les       |
+--------+----------------+

code_values

+-------+
| Field |
+-------+
| id    |
| pid   | -> Product ID
| codeid| 
| value |
+-------+

and finally example data:

+----+------+--------+--------------+
| id | pid  | codeid | value        |
+----+------+--------+--------------+
|  9 |    5 |      2 | ST 102 200 R |
| 10 |    5 |      3 | 12 000 33    |
| 11 |    6 |      2 | ST 343 432 R |
| 12 |    6 |      3 | 34 343 24    |
| 15 |    8 |      2 | ST 100 101 R |
| 16 |    8 |      3 | 11 223 34    |
| 17 |    0 |      2 | ST 343 432 R |
| 18 |    0 |      3 | 34 343 24    |
+----+------+--------+--------------+

I want to show like this into one query:

Product Columns | *code_keys rows > columns* |

+-----------+------+------+------+--------------+----------+
| productid | da   | ig   | des  | St Code      | Rear Les |
+-----------+------+------+------+--------------+----------+
|         5 | 78   | 33   | 23   | ST 102 200 R | 12 000 33| 
|         8 | 88   | 13   | 21   | ST 100 101 R | 11 223 34| 
+-----------+------+------+------+--------------+----------+

Any ideas?

Answer 1

You can do this using CASE statement:

SELECT p.productid, p.da, p.ig, p.des
      ,GROUP_CONCAT(CASE WHEN ck.title = 'St Code' 
                         THEN cv.value ELSE NULL END) AS 'St Code'
      ,GROUP_CONCAT(CASE WHEN ck.title = 'Rear Les' 
                         THEN cv.value ELSE NULL END) AS 'Rear Les'
FROM Products p
JOIN code_values cv ON p.productid = cv.pid
JOIN code_keys ck ON cv.codeid = ck.codeid
GROUP BY p.productid;

If you have unknown number of code_keys you can try this dynamic query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `title` = ''',
      `title`,
      ''' THEN cv.value ELSE NULL END) AS `',
      `title`, '`'
    )
  ) INTO @sql
FROM code_keys ck JOIN code_values cv ON cv.codeid = ck.codeid;

SET @sql = CONCAT('SELECT p.productid, p.da, p.ig, p.des, ', @sql,'
                     FROM Products p
                     JOIN code_values cv ON p.productid = cv.pid
                     JOIN code_keys ck ON cv.codeid = ck.codeid
                    GROUP BY p.productid
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

| PRODUCTID | DA | IG | DES |      ST CODE |  REAR LES |
--------------------------------------------------------
|         5 | 78 | 33 |  23 | ST 102 200 R | 12 000 33 |
|         8 | 88 | 13 |  21 | ST 100 101 R | 11 223 34 |

See this SQLFiddle