MySQL: how to pivot?

Say I have the following tables:

TABLE: foo
- foo_id (PK)
- foo_value

Table: bar
- bar_id (PK)
- foo_id (FK)
- var_value

There is a one to many relationship between foo and bar. How do I query this so that I get a result like this:

1, `foo`.`value`, `bar`.`value1`
2, `foo`.`value`, `bar`.`value1`, `bar`.`value2`
3, `foo`.`value`, `bar`.`value1`, `bar`.`value2`, `bar`.`value3`
4, `foo`.开发者_如何学JAVA`value`, `bar`.`value1`, `bar`.`value2`, `bar`.`value3` .... and so on

Short answer is that you can't.

A longer answer is that you can do this:

 SELECT foo.foo_id, foo_value, GROUP_CONCAT(var_value ORDER BY bar_id)
 FROM foo, bar
 WHERE bar.foo_id=bar.foo_id
 GROUP BY foo.foo_id, foo_value

However your example schema doesn't make a lot of sense. Consider instead where bar always has an indentifiable core set of, say 4 records, for each foo record, then bar would look something like:

• bar_id
• foo_id
• bar_type
• bar_value

Then you can do this:

SELECT foo_id, foo_value, 
(SELECT b1.var_value
 FROM bar b1
 WHERE b1.type=1),
(SELECT b2.var_value
 FROM bar b2
 WHERE b1.type=2),
(SELECT b3.var_value
 FROM bar b3
 WHERE b1.type=3),
(SELECT b4.var_value
 FROM bar b4
 WHERE b4.type=4)
FROM foo