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.