How to match column names and values from joining and related tables?

I have the following DB structure:

cars:

• id

• make

features:

• id

• name

cars_feature:

• id

• car_id

• feature_id

• value

I want to be able to select all cars with all features and return results with feature.name as the column name and cars_feature.value as the value. Right now I am able to get all feature and all values but I only figured out how to do that with group_concat.

What I am looking for is the following output:

car_id  car_make  color  wheels  doors
1       Ford      blue   alloy
2       Audi             alloy   3

Data example: SQLFiddle

With the information you gave, I think what you're trying to achieve is a Pivot table. I'd use CASE to make the pivot with your same query:

SELECT 
    cars.id as car_id, 
    cars.make as car_make, 
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color, 
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels, 
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
FROM cars
LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
LEFT JOIN features ON (car_feature.feature_id = features.id)
GROUP BY cars.id;

Test:

mysql> SELECT 
    -> cars.id as car_id, 
    ->     cars.make as car_make, 
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color, 
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels, 
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
    -> FROM cars
    -> LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
    -> LEFT JOIN features ON (car_feature.feature_id = features.id)
    -> GROUP BY cars.id;
+--------+----------+--------+--------+-------+
| car_id | car_make | color  | Wheels | Doors |
+--------+----------+--------+--------+-------+
|      1 | Ford     | Blue   | Alloy  |       |
|      2 | Audi     | Yellow |        | 4     |
+--------+----------+--------+--------+-------+
2 rows in set (0.00 sec)

mysql>

Try it on this Fiddle

StackExchange Database Administrators Q#118309, answer score: 2