Having a table as below:
| meta_id | post_id | meta_key | meta_value |
|---|---|---|---|
| 000001 | 1 | name1 | Sam |
| 000002 | 1 | tel1 | 12345678 |
| 000003 | 1 | name2 | Jackie |
| 000004 | 1 | tel2 | 23456789 |
| 000005 | 2 | name1 | David |
| 000006 | 2 | tel1 | 11111111 |
| 000007 | 2 | name2 | Mary |
| 000008 | 2 | tel2 | 22222222 |
And want the result to be:
| post_id | name1 | tel1 | name2 | tel2 |
|---|---|---|---|---|
| 1 | Sam | 12345678 | Jackie | 23456789 |
| 2 | David | 11111111 | Mary | 22222222 |
(Using MySQL on WordPress Database)
CodePudding user response：
You need to pivot your rows in the group.
select post_id,
max(case when meta_key = 'name1' then meta_value end) name1,
max(case when meta_key = 'tel1' then meta_value end) tel1,
max(case when meta_key = 'name2' then meta_value end) name2,
max(case when meta_key = 'tel2' then meta_value end) tel2
from tbl
group by post_id
Look at this fiddle.