Solved

MySQL Transform, Pivot or Cross Tab

Posted on 2010-08-31
1
1,058 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

15 Experts available now in Live!

Get 1:1 Help Now