Converting MySQL Tables

Question

I have a MySQL table with the layout like below:

---------------------------------------
| ID | TITLE       | VALUE  | PART NO |
---------------------------------------
| 1  | width (mm)  | 12     |    234  |
---------------------------------------
| 2  | colour      | red    |    234  |
--------------------------------------- 
| 3  | colour      | blue   |    235  |
---------------------------------------
| 4  | width (mm)  | 2      |    234  |
---------------------------------------  
| 5  | Part No.    | 235    |    235  |
---------------------------------------
| 6  | Part No.    | 234    |    234  |
---------------------------------------

I want the table to look like this:

----------------------------------------
| id  | Width (mm) | colour | Part No. |
----------------------------------------
| 1   | 12         | red    |   234    |
----------------------------------------
| 2   | 2          | blue   |   235    |
----------------------------------------

I found another answer but then lost the link - the answer didn't solve my problem because it would only work for digits.

I need the SQL code to account for the ( in the titles and words in the values (not just adding numbers).

I created the below SQL using the answer I found and lost - it works kinda but I'm missing tons of data it returns the "Manufacturer" column and the first "Alternative Partnumber" column but nothing else. (which is baffling me - there is weight and Height 100% in the table and those 2 fields are not close to each other in the query.)

SQL code

SELECT 
    part_number,
    part_number AS pn, 
        if( title = 'Alternative Partnumber', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Alternative Partnumber'), '' ) AS `Alternative Partnumber`,
        if( title = 'Amp', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Amp'), '' ) AS `Amp`,
        if( title = 'Amp Rating (Constant Torque)', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = "Amp Rating (Constant Torque)"), '' ) AS `Amp Rating (Constant Torque)`,
        if( title = 'Analogue Inputs', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = "Analogue Inputs"), '' ) AS "Analogue Inputs",
        if( title = 'Analogue output', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = "Analogue output"), '' ) AS "Analogue output",
        if( title = 'Manufacturer', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Manufacturer'), '' ) AS `Manufacturer`, 
        if( title = 'Weight', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Weight'), '' ) AS `Weight`,
        if( title = 'Width', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Width'), '' ) AS `Width`,
        if( title = 'Width (mm)', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Width (mm)'), '' ) AS `Width (mm)`
FROM 
    table_name 
GROUP BY 
    part_number;

Do I need another SQL to do the Group By? I think that is where it looses the data.

Thank you, Ryan

score 1 · Accepted Answer · answered May 13 '20 at 22:21

You can do conditional aggregation to pivot your EAV model, like so:

select 
    min(id) id,
    max(case when title = 'width (mm)' then value end) width_mm,
    max(case when title = 'color' then value end) colour,
    part_number
from mytable
group by part_number

Converting MySQL Tables

1 Answers1