Combining multiple rows into a single row with multiple columns

Question

I am trying to do a manual query for WordPress postmeta table which would combine two types of postmeta into one with multiple columns by post_id.

This is the structure of table:

--------------------------------------------------------
meta_id | post_id | meta_key                | meta_value
1       | 100     | _wp_attachment_metadata | a:6:{s:5:"width"}
2       | 100     | _thumbnail_id           | 23
`

This is the result I am trying to achieve:

--------------------------------------------------------
post_id | _wp_attachment_metadata | _thumbnail_id
100     | a:6:{s:5:"width"}       | 23

Answer 1

SELECT t1.post_id, t1.meta_value, t2.meta_value
FROM postmeta t1, postmeta t2
WHERE t1.post_id = t2.post_id
  AND t1.meta_key = '_wp_attachment_metadata'
  AND t2.meta_key = '_thumbnail_id'

or

SELECT post_id, 
       MAX(CASE WHEN meta_key = '_wp_attachment_metadata' 
                THEN meta_value END), 
       MAX(CASE WHEN meta_key = '_thumbnail_id' 
                THEN meta_value END)
FROM postmeta 
GROUP BY post_id

PS. First query skips post_id, if it have no one (any) of meta-keys, second query gives NULL for according output field in such case.