Dfyjkt

WordPress's wp_postmeta table has all the additional fields for a post but they are in rows so it's easy to add more.

However, now I want to query for all the fields of all the posts lets say, I obviously want those fields in a column and not a row.

This is my query that I am running

SELECT p.post_title, 
 m.meta_value, 
 m.meta_key 
FROM wp_posts p 
 JOIN wp_postmeta m 
 ON p.id = m.post_id 
WHERE p.id = 72697; 

This will give me all the meta_values and their respective meta keys as columns. But I need the meta keys values as columns and meta values as rows

For example a meta_key could be additional_description and it's value could be What's up

So I need something like this

SELECT p.post_title, additional_description
FROM wp_posts p 
 JOIN wp_postmeta m 
 ON p.id = m.post_id 
WHERE p.id = 72697; 

I need it as a column. I also need all of the posts and not a specific one, but whenever I remove the where it just doesn't query (I have lots of posts, that could be an issue).

Here is some sample data and how I want the results to show up
wp_postmeta table

meta_key post_id meta_key meta_value
1 5 total_related 5
2 5 updated 0
3 5 cricket 1
4 8 total_related 8
5 8 updated 1
6 8 cricket 0



wp_post table

id post_title other things I dont care about
5 This is awesome
8 This is more awesome

wp_post id is related to post_id on wp_postmeta table

Result wanted

post_title total_related updated cricket
This is awesome 5 0 1
This is more awesome 8 1 0 

What about something like this?

SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
 ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
 ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
 ON p.id = m3.post_id AND m3.meta_key = 'cricket'

And since you aren't looking for a specific post you should be able to do this.

If you want to query specific post_types you can try something like this

SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
 ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
 ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
 ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';

Try that:

select post_title , 
 MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
 MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
 MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket' 
FROM wp_posts p 
JOIN wp_postmeta m ON p.id = m.post_id 
GROUP BY p.id

There are several approaches.

Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:

SELECT p.post_title
 , ( SELECT m1.meta_value
 FROM wp_post_metadata m1
 WHERE m1.meta_key = 'total_related'
 AND m1.post_id = p.id
 ORDER BY m1.meta_key LIMIT 1
 ) AS `total_related`
 , ( SELECT m2.meta_value
 FROM wp_post_metadata m2
 WHERE m2.meta_key = 'updated'
 AND m2.post_id = p.id
 ORDER BY m2.meta_key LIMIT 1
 ) AS `updated`
 , ( SELECT m3.meta_value
 FROM wp_post_metadata m3
 WHERE m3.meta_key = 'cricket'
 AND m3.post_id = p.id
 ORDER BY m3.meta_key LIMIT 1
 ) AS `cricket`
 FROM wp_posts p
 WHERE p.id IN (5,8)

There are several other approaches, each with its own advantages and drawbacks.

There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)