Select rows as columns for wordpress post meta










4















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









share|improve this question
























  • sample data and wished result will be better .

    – echo_Me
    Jul 1 '14 at 18:12











  • @echo_Me added.

    – user1952811
    Jul 1 '14 at 18:18











  • This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table

    – spencer7593
    Jul 1 '14 at 18:18















4















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









share|improve this question
























  • sample data and wished result will be better .

    – echo_Me
    Jul 1 '14 at 18:12











  • @echo_Me added.

    – user1952811
    Jul 1 '14 at 18:18











  • This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table

    – spencer7593
    Jul 1 '14 at 18:18













4












4








4


2






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









share|improve this question
















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






php mysql sql wordpress






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 '18 at 5:53









Cœur

19k9112154




19k9112154










asked Jul 1 '14 at 18:08









user1952811user1952811

1,05332143




1,05332143












  • sample data and wished result will be better .

    – echo_Me
    Jul 1 '14 at 18:12











  • @echo_Me added.

    – user1952811
    Jul 1 '14 at 18:18











  • This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table

    – spencer7593
    Jul 1 '14 at 18:18

















  • sample data and wished result will be better .

    – echo_Me
    Jul 1 '14 at 18:12











  • @echo_Me added.

    – user1952811
    Jul 1 '14 at 18:18











  • This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table

    – spencer7593
    Jul 1 '14 at 18:18
















sample data and wished result will be better .

– echo_Me
Jul 1 '14 at 18:12





sample data and wished result will be better .

– echo_Me
Jul 1 '14 at 18:12













@echo_Me added.

– user1952811
Jul 1 '14 at 18:18





@echo_Me added.

– user1952811
Jul 1 '14 at 18:18













This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table

– spencer7593
Jul 1 '14 at 18:18





This question has been asked and answered several times. There are several approaches, each with advantages and drawbacks. The general problem you are addressing is the EAV model. A brief example of several approaches can be found here: http://stackoverflow.com/questions/8764290/what-is-best-performance-for-retrieving-mysql-eav-results-as-relational-table

– spencer7593
Jul 1 '14 at 18:18












3 Answers
3






active

oldest

votes


















7














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





share|improve this answer























  • Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

    – user1952811
    Jul 1 '14 at 19:04











  • Worked for me.thanks +10

    – Alive to Die
    Dec 19 '16 at 20:19


















2














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





share|improve this answer

























  • @spencer7593 why group by because he have just one id WHERE p.id = 72697

    – echo_Me
    Jul 1 '14 at 18:32











  • I need to query more then one post

    – user1952811
    Jul 1 '14 at 18:36











  • @user1952811 check edited answer.

    – echo_Me
    Jul 1 '14 at 18:37


















2














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.)






share|improve this answer
























    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f24516710%2fselect-rows-as-columns-for-wordpress-post-meta%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    7














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





    share|improve this answer























    • Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

      – user1952811
      Jul 1 '14 at 19:04











    • Worked for me.thanks +10

      – Alive to Die
      Dec 19 '16 at 20:19















    7














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





    share|improve this answer























    • Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

      – user1952811
      Jul 1 '14 at 19:04











    • Worked for me.thanks +10

      – Alive to Die
      Dec 19 '16 at 20:19













    7












    7








    7







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





    share|improve this answer













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






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Jul 1 '14 at 18:42









    MaazMaaz

    2,29422145




    2,29422145












    • Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

      – user1952811
      Jul 1 '14 at 19:04











    • Worked for me.thanks +10

      – Alive to Die
      Dec 19 '16 at 20:19

















    • Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

      – user1952811
      Jul 1 '14 at 19:04











    • Worked for me.thanks +10

      – Alive to Die
      Dec 19 '16 at 20:19
















    Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

    – user1952811
    Jul 1 '14 at 19:04





    Cleanest / easily readable one. And I do need the post_type condition, thanks for adding that in!

    – user1952811
    Jul 1 '14 at 19:04













    Worked for me.thanks +10

    – Alive to Die
    Dec 19 '16 at 20:19





    Worked for me.thanks +10

    – Alive to Die
    Dec 19 '16 at 20:19













    2














    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





    share|improve this answer

























    • @spencer7593 why group by because he have just one id WHERE p.id = 72697

      – echo_Me
      Jul 1 '14 at 18:32











    • I need to query more then one post

      – user1952811
      Jul 1 '14 at 18:36











    • @user1952811 check edited answer.

      – echo_Me
      Jul 1 '14 at 18:37















    2














    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





    share|improve this answer

























    • @spencer7593 why group by because he have just one id WHERE p.id = 72697

      – echo_Me
      Jul 1 '14 at 18:32











    • I need to query more then one post

      – user1952811
      Jul 1 '14 at 18:36











    • @user1952811 check edited answer.

      – echo_Me
      Jul 1 '14 at 18:37













    2












    2








    2







    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





    share|improve this answer















    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






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Jul 1 '14 at 18:37

























    answered Jul 1 '14 at 18:27









    echo_Meecho_Me

    32.9k54170




    32.9k54170












    • @spencer7593 why group by because he have just one id WHERE p.id = 72697

      – echo_Me
      Jul 1 '14 at 18:32











    • I need to query more then one post

      – user1952811
      Jul 1 '14 at 18:36











    • @user1952811 check edited answer.

      – echo_Me
      Jul 1 '14 at 18:37

















    • @spencer7593 why group by because he have just one id WHERE p.id = 72697

      – echo_Me
      Jul 1 '14 at 18:32











    • I need to query more then one post

      – user1952811
      Jul 1 '14 at 18:36











    • @user1952811 check edited answer.

      – echo_Me
      Jul 1 '14 at 18:37
















    @spencer7593 why group by because he have just one id WHERE p.id = 72697

    – echo_Me
    Jul 1 '14 at 18:32





    @spencer7593 why group by because he have just one id WHERE p.id = 72697

    – echo_Me
    Jul 1 '14 at 18:32













    I need to query more then one post

    – user1952811
    Jul 1 '14 at 18:36





    I need to query more then one post

    – user1952811
    Jul 1 '14 at 18:36













    @user1952811 check edited answer.

    – echo_Me
    Jul 1 '14 at 18:37





    @user1952811 check edited answer.

    – echo_Me
    Jul 1 '14 at 18:37











    2














    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.)






    share|improve this answer





























      2














      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.)






      share|improve this answer



























        2












        2








        2







        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.)






        share|improve this answer















        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.)







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jul 1 '14 at 18:41

























        answered Jul 1 '14 at 18:26









        spencer7593spencer7593

        86k118197




        86k118197



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f24516710%2fselect-rows-as-columns-for-wordpress-post-meta%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            𛂒𛀶,𛀽𛀑𛂀𛃧𛂓𛀙𛃆𛃑𛃷𛂟𛁡𛀢𛀟𛁤𛂽𛁕𛁪𛂟𛂯,𛁞𛂧𛀴𛁄𛁠𛁼𛂿𛀤 𛂘,𛁺𛂾𛃭𛃭𛃵𛀺,𛂣𛃍𛂖𛃶 𛀸𛃀𛂖𛁶𛁏𛁚 𛂢𛂞 𛁰𛂆𛀔,𛁸𛀽𛁓𛃋𛂇𛃧𛀧𛃣𛂐𛃇,𛂂𛃻𛃲𛁬𛃞𛀧𛃃𛀅 𛂭𛁠𛁡𛃇𛀷𛃓𛁥,𛁙𛁘𛁞𛃸𛁸𛃣𛁜,𛂛,𛃿,𛁯𛂘𛂌𛃛𛁱𛃌𛂈𛂇 𛁊𛃲,𛀕𛃴𛀜 𛀶𛂆𛀶𛃟𛂉𛀣,𛂐𛁞𛁾 𛁷𛂑𛁳𛂯𛀬𛃅,𛃶𛁼

            Crossroads (UK TV series)

            ữḛḳṊẴ ẋ,Ẩṙ,ỹḛẪẠứụỿṞṦ,Ṉẍừ,ứ Ị,Ḵ,ṏ ṇỪḎḰṰọửḊ ṾḨḮữẑỶṑỗḮṣṉẃ Ữẩụ,ṓ,ḹẕḪḫỞṿḭ ỒṱṨẁṋṜ ḅẈ ṉ ứṀḱṑỒḵ,ḏ,ḊḖỹẊ Ẻḷổ,ṥ ẔḲẪụḣể Ṱ ḭỏựẶ Ồ Ṩ,ẂḿṡḾồ ỗṗṡịṞẤḵṽẃ ṸḒẄẘ,ủẞẵṦṟầṓế