SQL PIVOT after an INNER JOIN

debugcn 投稿 Dev

Bastien Bastiens

I'm trying to create and event page from the data in my wordpress database. The data are saved in 2 tables ($wp_posts and $wp_postmeta). After the INNER JOIN i need to PIVOT the table $wp_postmeta but after multiple tries i cannot figure out how to do it.

My Query is :

SELECT
PO.ID,
PO.post_title,
PO.post_content,
PM.post_id,
PM.meta_key,
PM.meta_value

FROM $wp_posts PO
INNER JOIN $wp_postmeta PM ON PO.ID = PM.post_id

I need to have the "meta_key" from the table $wp_postmeta to become column names then to be able to select meta_values

Database

Thank you for your help with my query.

John Ruddell

MySQL doesn't have the ability to run a pivot.. but you can fake a pivot.

NOTE:

you need to know the number of rows you want to pivot.

QUERY:

SELECT
    MAX(CASE meta_key WHEN '_EventOrganizerID' THEN meta_value END )AS _EventOrganizerID,
    MAX(CASE meta_key WHEN '_EventURL' THEN meta_value END )AS _EventURL,
    MAX(CASE meta_key WHEN '_EventCost' THEN meta_value END )AS _EventCost,
    MAX(CASE meta_key WHEN '_EventCurrencyPosition' THEN meta_value END )AS _EventCurrencyPosition,
    MAX(CASE meta_key WHEN '_EventCurrencySymbol' THEN meta_value END)AS _EventCurrencySymbol,
    MAX(CASE meta_key WHEN '_EventVenueID' THEN meta_value END )AS _EventVenueID,
    MAX(CASE meta_key WHEN '_EventDuration' THEN meta_value END )AS _EventDuration,
    MAX(CASE meta_key WHEN '_EventEndDate' THEN meta_value END )AS _EventEndDate,
    MAX(CASE meta_key WHEN '_EventStartDate' THEN meta_value END )AS _EventStartDate,
    MAX(CASE meta_key WHEN '_EventAllDay' THEN meta_value END )AS _EventAllDay
FROM
(   SELECT
        PO.ID,
        PO.post_title,
        PO.post_content,
        PM.post_id,
        PM.meta_key,
        PM.meta_value
    FROM $wp_posts PO
    INNER JOIN $wp_postmeta PM ON PO.ID = PM.post_id
) t
GROUP BY post_id

ANOTHER NOTE:

if you want to select the meta_values from this query.. as it not just pivot the result but actually select specific stuff you can select from this query like I did to pivot it.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集2021-06-29

コメントを追加

サインイン

分類Dev

Related 関連記事

記事