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?
