MySQL Transform, Pivot or Cross Tab

I want to perform a (Transform, Pivot or Cross Tab) on my data so I can join it to another file.

I have two database tables that are used to append customer designated product fields onto a flat products table

Here's what I have so far that does not work.

SELECT cp.product_id,
    if(cp.code='brand', cp.value, ' ') AS brand,
    if(cp.code='size', cp.value, ' ') AS size,
    if(cp.code='cigarlength', cp.value, ' ') AS cigarlength,
    if(cp.code='ringgauge', cp.value, ' ') AS ringgauge,
    if(cp.code='origin', cp.value, ' ') AS origin,
    if(cp.code='wrapper', cp.value, ' ') AS wrapper
FROM (
    SELECT cpv.product_id, cpf.code, cpv.field_id, cpv.value
    FROM s01_CFM_ProdValues cpv
        LEFT JOIN s01_CFM_ProdFields cpf ON cpf.id = cpv.field_id
    WHERE cpf.code IN('brand','size','cigarlength','ringgauge','cigarstrength','origin','wrapper')
    ) cp
GROUP BY cp.product_id

Both examples I used as a reference use sum() and are dealing with numbers instead of strings.
http://forums.mysql.com/read.php?10,81701,81701
http://en.wikibooks.org/wiki/MySQL/Pivot_table

what I want is this.
product_id | brand        | size      | cigarlength | ringgauge | cigarstrength | origin    | wrapper 
-----------+--------------+-----------+-------------+-----------+---------------+-----------+----------
2875       | 1876 Reserve | Churchill | 7           | 5         | MILD          | DOMINICAN | NATURAL
2876       | 1876 Reserve | Robusto   | 5           | 4.5       | MILD          | DOMINICAN | NATURAL

Open in new window

Here are the tables.
Table: s01_CFM_ProdFields contain the name of the code and fields and few records

id | code          | name
---+---------------+----------------
14 | brand         | Brand
15 | size          | Size
17 | cigarlength   | Cigar Length
18 | ringgauge     | Ring Gauge
19 | cigarstrength | Cigar Strength
20 | origin        | Origin
21 | wrapper       | Wrapper


Table: s01_CFM_ProdValues contains the actual data and many records 
field_id  | product_id | value
----------+------------+------------
14        |        500 |  data1
15        |        500 |  data2
17        |        500 |  data3
18        |        500 |  data4
19        |        500 |  data5
20        |        500 |  data6
14        |       1200 |  data1
15        |       1200 |  data2
17        |       1200 |  data3
18        |       1200 |  data4
19        |       1200 |  data5
20        |       1200 |  data7

I join them to gether using this.

SELECT cpv.product_id, cpf.code, cpv.field_id, cpv.value
FROM s01_CFM_ProdValues cpv
    LEFT JOIN s01_CFM_ProdFields cpf ON cpf.id = cpv.field_id
WHERE cpf.code IN('brand','size','cigarlength','ringgauge','cigarstrength','origin','wrapper')

Resulting in:

product_id | code          | field_id | value
-----------+---------------+----------+-------------------
2875       | brand         |       14 | 1876 Reserve 
2875       | size          |       15 | Churchill 
2875       | cigarlength   |       17 | 7 
2875       | ringgauge     |       18 | 5 
2875       | cigarstrength |       19 | MILD 
2875       | origin        |       20 | DOMINICAN 
2875       | wrapper       |       21 | NATURAL 
2876       | brand         |       14 | 1876 Reserve 
2876       | size          |       15 | Robusto 
2876       | cigarlength   |       17 | 5 
2876       | ringgauge     |       18 | 4.5 
2876       | cigarstrength |       19 | MILD 
2876       | origin        |       20 | DOMINICAN REPUBLIC 
2876       | wrapper       |       21 | NATURAL 

I want to transform them like this.

product_id | brand        | size      | cigarlength | ringgauge | cigarstrength | origin    | wrapper 
-----------+--------------+-----------+-------------+-----------+---------------+-----------+----------
2875       | 1876 Reserve | Churchill | 7           | 5         | MILD          | DOMINICAN | NATURAL
2876       | 1876 Reserve | Robusto   | 5           | 4.5       | MILD          | DOMINICAN | NATURAL

Open in new window

LVL 1
kiqkinasAsked:
Who is Participating?
 
kiqkinasConnect With a Mentor Author Commented:
I found the solution.

SELECT cp.product_id,
    MAX(if(cp.code='brand', cp.value, ' ')) AS brand,
    MAX(if(cp.code='size', cp.value, ' ')) AS size,
    MAX(if(cp.code='cigarlength', cp.value, ' ')) AS cigarlength,
    MAX(if(cp.code='ringgauge', cp.value, ' ')) AS ringgauge,
    MAX(if(cp.code='origin', cp.value, ' ')) AS origin,
    MAX(if(cp.code='wrapper', cp.value, ' ')) AS wrapper
FROM (
    SELECT cpv.product_id, cpf.code, cpv.field_id, cpv.value
    FROM s01_CFM_ProdValues cpv
        LEFT JOIN s01_CFM_ProdFields cpf ON cpf.id = cpv.field_id
    WHERE cpf.code IN('brand','size','cigarlength','ringgauge','cigarstrength','origin','wrapper')
    ) cp
GROUP BY cp.product_id
0
All Courses

From novice to tech pro — start learning today.