CODEWITHSUNDEEP
mysqljoinpivot-table
MarkL
MarkL

Reputation: 9100

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

I have the following DB structure:

cars:

features:

cars_feature:

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

Upvotes: 0

Views: 100

Answers (1)

toonice
toonice

Reputation: 2246

The following is a modified version of code that I found at Combine multiple rows into one row MySQL.

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(CASE WHEN `name` = ''',
      `name`,
      ''' THEN value END) AS `',
      `name`, '`'
    )
  ) INTO @sql
FROM (SELECT name,
             value
      FROM features, car_feature
      WHERE features.id = car_feature.feature_id) AS car_features_feature;

SET @sql = CONCAT('SELECT car_feature.car_id AS car_id,
                          cars.make AS car_make,
                          ',
                          @sql,
                          '
                   FROM car_feature
                   LEFT JOIN cars ON car_feature.car_id = cars.id
                   LEFT JOIN features ON car_feature.feature_id = features.id
                   GROUP BY car_id;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Thank you for the learning experience.

Upvotes: 1

Related Questions