Carlvic Japitana Lim Carlvic Japitana Lim - 4 months ago 17
SQL Question

Querying MYSQL post_meta to new format of table

Hi I have a existing table which is

post_meta

----------------------------------------------------------------
| meta_id | post_id | meta_key | meta_value |
----------------------------------------------------------------
| 1 | 1 | field1 | John Doe |
| 2 | 1 | field2 | jonedoe@gmail.com |
| 3 | 1 | field3 | 099999999 |
| 4 | 2 | field1 | Err ManMa |
| 5 | 2 | field2 | errmanma@gmail.com |
| 6 | 2 | field3 | 09231412312 |
----------------------------------------------------------------


How can I create or format my post_meta table like this?

----------------------------------------------------------------
| meta_id | field1 | field2 | field3 |
----------------------------------------------------------------
| 1 | John Doe | jonedoe@gmail.com | 099999999 |
| 2 | Err ManMa | errmanma@gmail.com | 09231412312 |
----------------------------------------------------------------


Thanks for the help

Answer

I think your result columns should be post_id, field1, field2, field3 and then this is a table pivot issue, try this:

select
    post_id,
    max(case when meta_key = 'field1' then meta_value end) as field1,
    max(case when meta_key = 'field2' then meta_value end) as field2,
    max(case when meta_key = 'field3' then meta_value end) as field3
from post_meta
group by post_id

SQLFiddle Demo

.