CODEWITHSUNDEEP
mysqlsql
andy
andy

Reputation: 459

Wordpress Multiple DB Rows Into Columns

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.

Upvotes: 1

Views: 88

Answers (2)

D-Shih
D-Shih

Reputation: 46219

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 |

Upvotes: 2

2189490
2189490

Reputation: 137

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

Upvotes: 0

Related Questions