Select each subquery row as a column (or: column to row conversion)

[epp_b]

So, this one's got me stumped. Let's say I have these tables:

Table: Cars
+-------+-------+
| CarID | Make  |
+-------+-------+
| 1     | Ford  |
| 2     | Chevy |
| 3     | Dodge |
+-------+-------+

Table: People
+-------+-------+---------+-------+
| PerID | Name  | Phone   | Email |
+-------+-------+---------+-------+
| 1     | Alice | 1234567 | a@a.a |
| 2     | Bob   | 8912345 | b@b.b |
| 3     | Chuck | 5678912 | c@c.c |
+-------+-------+---------+-------+

Table: CarsOwnedByPeople
+-------+-------+
| PerID | CarID |
+-------+-------+
| 1     | 1     | 
| 2     | 2     | 
| 3     | 3     |
| 3     | 2     |        
+-------+-------+

This isn't the actual data I'll be using (it's actually a educational group membership program), but I think this analogy is easier to explain.

So, we can see that:

Alice owns Ford, !Chevy, !Dodge
Bob owns Chevy, !Ford, !Dodge
Chuck owns Dodge, Chevy, !Ford

Now, I need to get a list of ownership status of each car for each person. The catch is that I need to have them each in their own column (not CONCATed or GROUP_CONCATed into one column) and I'm trying to avoid doing a COUNT(*) on the Cars table.

What I need to get as a result is:

+-------+---------+-------+------+-------+-------+
| Name  | Phone   | Email | Ford | Chevy | Dodge |
+-------+---------+-------+------+-------+-------+
| Alice | 1234567 | a@a.a | 1    | 0     | 0     |
| Bob   | 8912345 | b@b.b | 0    | 1     | 0     |
| Chuck | 5678912 | c@c.c | 0    | 1     | 1     |
+-------+---------+-------+------+-------+-------+

So, basicaly, I need to convert the rows of the Cars table into columns in this results table.

Is this even possible?

[r937]

yeah, it's possible, but it's ugly and clumsy in sql

please explain why it's so hard for you to do this in your application language (php or whatever), which is where cosmetic re-arranging of query results should be taking place...

[epp_b]

Well, yes, of course I can do this in PHP, but I figured it would be more eloquent and efficient with MySQL.

Also, curiosity

[guelphdad]

it is the opposite, harder to do in sql, easier to do in the front end application. As r937 says, that's where cosmetic changes to the data belong.

[epp_b]

Okeydokey.

[epp_b]

...but just to the point of curiosity, how would one go about doing this within MySQL anyway?

[squeal]

It's called a PIVOT TABLE query. Google for that and you should be able to find both examples of such queries, and further evidence in support of why it's often wiser to handle this at the application level.