Wordpress Multiple DB Rows Into Columns

Question

I want to retrieve the data of multiple product meta values for the same product but instead of displaying it in multiple rows I want the meta values as their own columns.

I start with:

   SELECT a.post_title, b.meta_key, b.meta_value
   FROM wp_posts a
   INNER JOIN wp_postmeta b
   ON a.ID = b.post_id
   WHERE a.post_type = 'product' 
   AND b.meta_key = '_custom_animal_id' 
   OR b.meta_key = '_custom_breed';

This returns a table:

Name	meta_key	meta_value
Cow 1	_custom_animal_id	FR4380
Cow 1	_custom_breed	HO
Cow 2	_custom_animal_id	FR0001
Cow 2	_custom_breed	HO
Cow 3	_custom_animal_id	FR9999
Cow 3	_custom_breed	ZZ

What I want to display is:

Name	Animal ID	Breed
Cow 1	FR4380	HO
Cow 2	FR0001	HO
Cow 3	FR9999	ZZ

I know I will need to group by post_id (as a the title may not be unique) but returning the rows to columns I have searched a lot and not finding the answer to do it.

D-Shih · Accepted Answer · 2022-01-07 17:17:50Z

2

You can try to use the condition aggregate function.

MAX with CASE WHEN to make the pivot

SELECT a.post_title 'Name', 
       MAX(CASE WHEN b.meta_key = '_custom_animal_id' THEN b.meta_value END) 'Animal ID' , 
       MAX(CASE WHEN b.meta_key = '_custom_breed' THEN b.meta_value END) 'Breed'
FROM wp_posts a
INNER JOIN wp_postmeta b
ON a.ID = b.post_id
WHERE a.post_type = 'product' 
AND b.meta_key = '_custom_animal_id' 
OR b.meta_key = '_custom_breed'
GROUP BY a.post_title

Results:

|  Name | Animal ID | Breed |
|-------|-----------|-------|
| Cow 1 |    FR4380 |    HO |
| Cow 2 |    FR0001 |    HO |
| Cow 3 |    FR9999 |    ZZ |

edited Jan 7, 2022 at 17:17

answered Jan 7, 2022 at 17:12

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

2189490 · Accepted Answer · 2022-01-07 17:10:34Z

0

Need to make use of a subquery here to get what we need. Make sure you have an appropriate index on wp_postmeta.meta_key if you haven't already.

SELECT a.post_title, animalid.meta_key, breed.meta_key
FROM wp_posts a
INNER JOIN (SELECT post_id, meta_key
            FROM wp_postmeta
            WHERE meta_key = '_custom_animal_id') animalid ON a.ID = animalid.post_id
INNER JOIN (SELECT post_id, meta_key
            FROM wp_postmeta
            WHERE meta_key = '_custom_breed') breed ON a.ID = breed.post_id
WHERE a.post_type = 'product';

edited Jan 7, 2022 at 17:10

answered Jan 7, 2022 at 17:03

2189490

1399 bronze badges

2 Comments

andy Over a year ago

I need custom animal ID as you can see in the column in the 2nd table.

2189490 Over a year ago

Oh I see, give me a moment to revise my query

Collectives™ on Stack Overflow

Wordpress Multiple DB Rows Into Columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related