SELECT multiple values from a single column in Wordpress Database

Question

I am trying to output the post details, as well as some custom fields in wordpress's database. I have custom fields called 'thumbnail' and 'video'

SELECT ID, post_title, post_content, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key = "thumnail"
OR wp_postmeta.meta_key = "video"

So far I am getting individual rows like this:

| ID  | Title     | Content     | Custom Fields                      |
|--------------------------------------------------------------------|
| 234 | INCEPTION | Content etc | wp-content/thumbnail/inception.jpg |
| 234 | INCEPTION | Content etc | wp-content/video/inception.flv     |

but what i would like is (ie one line per post)

|ID |Title     |Content     |Thumbnail                  |Video                      |
|-----------------------------------------------------------------------------------|
|234|INCEPTION |Content etc |wp-content/..inception.jpg |wp-content/..inception.flv |

Could someone advise on how to separate the different values into columns within the SELECT statement?

Consider providing proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET — Strawberry, Jun 23 '14 at 22:59

score 3 · Accepted Answer · answered Jun 23 '14 at 23:35

3

This process is called "pivot table" or "crosstab report". There is no PIVOT command in MySQL but it can be done manually, here are two examples. The first example uses IF statement, the second one CASE statement. And an aggregate function is used to get rid of the duplicate rows.

SELECT ID, post_title, post_content,
MAX( IF ( wp_postmeta.meta_key = "thumnail", wp_postmeta.meta_value, '' ) ) AS Thumbnail,
MAX( IF ( wp_postmeta.meta_key = "video", wp_postmeta.meta_value, '' ) ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN ( "thumnail", "video" )
GROUP BY ID

SELECT ID, post_title, post_content,
MAX( CASE WHEN wp_postmeta.meta_key = "thumnail" THEN wp_postmeta.meta_value ELSE '' END ) AS Thumbnail,
MAX( CASE WHEN wp_postmeta.meta_key = "video" THEN wp_postmeta.meta_value ELSE '' END ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN( "thumnail", "video" )
GROUP BY ID

answered Jun 23 '14 at 23:35

Danijel

12,408
5
38
54

Thanks this works great, after reading up on IF Statements I cant figure out the logic of what you're doing with MAX, works but want do understand why?? – southafricanrob Jun 24 '14 at 10:45
Aggregate function is used when grouping, without GROUP BY output will have duplicate rows, and without MAX ( SUM or COUNT can be used also ) it will have empty values. Check [this](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns/9668036#9668036) answer for better explanation. – Danijel Jun 24 '14 at 11:47
Aha, makes sense - thanks. Whilst here, could you offer advice on how to add the post categories to this - I have used the same logic in your answer but come unstuck as there is no unique value (ie as in meta_key), they are all just 'category'. I can output the first, but not all... – southafricanrob Jun 24 '14 at 18:32
Perhaps it would be better to ask a new question, so that others can participate also, and to get a better view of the problem. By the way, if this answer helped then maybe you should mark the answers as Accepted. – Danijel Jun 24 '14 at 18:51

SELECT multiple values from a single column in Wordpress Database

1 Answers1