mysql variants to columns from sql query

Question

I have a mysql table which has for simplified purposes sku, and that sku has an unknown set of attribute_values

skus
------
id, sku, qty
1, abc, 10
2, bvcc, 20

variantvalues
-------------
id, sku_id, variant_id, value
1, 1, 1, red
1, 1, 2, medium

variants
--------
id, name 
1, color
2, size
.....

EXPECTED RESULT
id, sku, color, size, qty
-------------------------
1, abc, red, medium, 10

the challenge I'm having is, can I do a query that gives me the data as a list of skus, and then columns for each of their variants and variant value... such that, if I added a new variant option... say, gender, to the variants table, and it was attributed to a sku, then there would be a new column for that?

The question is derived from this question: Modeling Product Variants

Answer 1

You are describing a dynamic pivot. Basically you need to dynamically generate the query string, using a sql query, then execute it.

Here is one way to do it in MySQL, using a prepared statement:

set @sql = null;

select group_concat(distinct 
    'max(case when v.name = ''', name, ''' then vv.value end) as `', name, '`'
) into @sql
from variants;

set @sql = concat(
    'select s.id, s.sku, ',  
    @sql,
    ' from skus s
    inner join variantvalues vv on vv.sku_id = s.id
    inner join variants v on v.id = vv.variant_id
    group by s.id, s.sku'
);

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;