mysql - MySQL行作为列

我有这个查询结果，我希望将每个id的第二行添加为

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.

现在的结果是：

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

我希望是

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

最佳答案

实际上，您并没有试图将行转换为列。您正在尝试将查询的两行合并为一行。这种行为应该可以通过加入两次事件来实现。

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

这样m1-join将地址作为列添加，而m2-join将添加日期。我不能测试这个，但我很确定它是有效的（模块一些打字语法错误）。