CODEWITHSUNDEEP
mysqlsql
Onisiforos
Onisiforos

Reputation: 15

MySQL row as a column

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

Upvotes: 1

Views: 53

Answers (1)

SebastianH
SebastianH

Reputation: 2182

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).

Upvotes: 2

Related Questions