MySQL - Combinations / pivot

Question

I’ve a problem that is very similar to this one:
How to pivot a MySQL entity-attribute-value schema
or this one
Enumerating combinations via SQL

I’ve two tables:

variations

ID | item_id | name
=================================
 1 | 1       | color
 2 | 1       | size
 3 | 1       | material
 4 | 1       | lenght
==================================

variation_data

ID | variation_id | value
=================================
 1 | 1            | red
 2 | 1            | white
 3 | 1            | black
 4 | 2            | s
 5 | 2            | m
 6 | 3            | cotton
 7 | 4            | 100
==================================

Name and value are user inputs.
There are up to 4 variations per item an n values per variation.

Some test data

CREATE TABLE variations ( id int PRIMARY KEY, item_id int, name varchar(50));
INSERT INTO variations (id, item_id, name) 
VALUES (1, 1, 'color'),
       (2, 1, 'size'),
       (3, 1, 'material'),
       (4, 1, 'length');

CREATE TABLE variation_data ( id int PRIMARY KEY, variation_id int, val varchar(50));
INSERT INTO variation_data (id, variation_id, value) VALUES
  (1, 1, 'red'),
  (2, 1, 'white'),
  (3, 1, 'black'),
  (4, 2, 's'),
  (5, 2, 'm'),
  (6, 3, 'cotton'),
  (7, 4, '100');

Desired result:

red     S   cotton  100
red     M   cotton  100
white   S   cotton  100
white   M   cotton  100
black   S   cotton  100
black   M   cotton  100

Unfortunately I’m not able to solve this problem.
I hope you are able to give me a hint.

Thanks for your support!

Answer 1

I could suggest you this query -

SELECT v.item_id,
  GROUP_CONCAT(IF(v.name = 'color', vd.val, NULL)) color,
  GROUP_CONCAT(IF(v.name = 'size', vd.val, NULL)) size,
  GROUP_CONCAT(IF(v.name = 'material', vd.val, NULL)) material,
  GROUP_CONCAT(IF(v.name = 'length', vd.val, NULL)) length
FROM variations v
  JOIN variation_data vd
    ON vd.variation_id = v.id
GROUP BY v.item_id

It doesn't return desired result, but it does 'pivot'.