Solved

MySQL Transform, Pivot or Cross Tab

Posted on 2010-08-31
1
1,054 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:kiqkinas
1 Comment
 
LVL 1

Accepted Solution

by:
kiqkinas earned 0 total points
ID: 33569655
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now