MySQL row as a column

Question

I have this query result and I would like the second row for each id to be added as a

SELECT m.post_id,m.meta_value,p.post_title 
from events_posts p
inner join events_postmeta m 
on p.ID=m.post_id and p.post_type='event' and (p.post_status='publish' or p.post_status='recurring') 
and(m.meta_key='address' or m.meta_key='st_date')
order by m.post_id
 column.

The results right now :

post_id meta_value  post_title
15  Carolina Beach Road, Wilmington, NC, United States  An Art Exhibition
15  2014-02-03  An Art Exhibition
19  Dakota Street, Winnipeg, MB, Canada Weekly Karate Classes
19  2014-02-06  Weekly Karate Classes
23  Alaskan Way, Seattle, WA, United State  Christmas Carnival
23  2014-02-03  Christmas Carnival

I would like it to be

post_id | meta_value| post_title

15  | Carolina Beach Road, Wilmington, NC, United States    | An Art Exhibition   | 2014-02-03  
19  |Dakota Street, Winnipeg, MB, Canada    |Weekly Karate Classes |2014-02-06   
23  |Alaskan Way, Seattle, WA, United State |Christmas Carnival |2014-02-03

Can you please add the description of the events_postmeta table? I assume there's a metadata name field which can be used to distinguish between address and date metadata which can be used as part of the join criteria to have two logical joins — PaulProgrammer
– PaulProgrammer, Commented Feb 5, 2014 at 17:44

SebastianH · Accepted Answer · 2014-02-05 17:46:02Z

2

You are actually not trying to convert rows to columns. You are trying to merge two rows of your query into one. This behaviour should be possible through joining the events_postmeta twice.

SELECT p.ID, m1.meta_value as event_address, m2.meta_value as event_date, p.post_title 
from events_posts p
inner join events_postmeta m1
on p.ID=m1.post_id and p.post_type='event' and (p.post_status='publish' or p.post_status='recurring') 
and m1.meta_key='address'
inner join events_postmeta m2
on p.ID=m2.post_id and p.post_type='event' and (p.post_status='publish' or p.post_status='recurring') 
and m2.meta_key='st_date'
order by p.ID

That way the m1-join adds the address as a column and the m2-join adds the date. I could not test this but I am pretty sure it works (modulo some typing syntax mistakes).

answered Feb 5, 2014 at 17:46

SebastianH

2,1821 gold badge19 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

MySQL row as a column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related