Select rows as columns for wordpress meta post

Question

The WordPress table wp_postmetahas all the extra fields for a post, but they are in rows, so they are easy to add.

However, now I want to request for all fields of all messages that, say, I obviously need these fields in a column, not a row.

This is my request that I run

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 corresponding meta keys as columns. But I need meta-key values ​​as columns and meta-values ​​as rows

For example, meta_keymaybe additional_description, and this value can beWhat 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; 

. , , , where, ( , ).

, wp_postmeta​​p >

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 post_id wp_postmeta​​p >

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

Answer 1

- ?

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'

, .

post_types, -

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';

Answer 2

.

, SELECT:

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)

, .

, . , .)

Answer 3

:

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