Mysql dynamic row values as column names

Question

Recently i ran into a project with following structure

http://sqlfiddle.com/#!2/3a4cf/1

all works ok, i think i can sort the things on PHP end, but my question is, is it possible with a single query

number, name, age, email, gender
--------------------------------------------
123456, Test1, 24, test1@test.com, m
123457, Test2, 23, test2@test.com, f
123458, Test3, 22, test3@test.com, m
123459, Test4, 21, test4@test.com, f
123460, Test5, 25, test5@test.com, m

considering column names to be the actual field.field_name

I tried with multiple left/right joins (as many fields as the number has) but that can go up to infinity lol ... i.e. having 100 fields that will be 200 joins to get the result in a single row (still with no column names as the field_value).

I took a look into following (possible duplicate):

mysql select dynamic row values as column names, another column as value

but doesnt talk much to me (not that experienced in mysql :( ).

If possible i'd be grateful if anyone can guide me a bit on how to achieve it.

Thanks in advance.

Answer 1

I think you want this query

SELECT SQL_NO_CACHE
    n.number, n.name, 
MAX(CASE WHEN f1.field_name='age' THEN nf1.value END) as Age,
MAX(CASE WHEN f1.field_name='email' THEN nf1.value END) as Email,
MAX(CASE WHEN f1.field_name='gender' THEN nf1.value END) as Gender
FROM
    number AS n
        LEFT JOIN
    number_field AS nf1 ON n.id = nf1.number_id
        RIGHT JOIN
    field AS f1 ON f1.id = nf1.field_id
WHERE
    1 = 1
GROUP BY n.number,n.name
ORDER BY number

FIDDLE

Dynamic

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN f1.field_name= ''',
      f1.field_name,
      ''' THEN nf1.`value` END) AS ',
      f1.field_name
    )
   )INTO @sql
FROM  
    number_field AS nf1 
        RIGHT JOIN
    field AS f1 ON f1.id = nf1.field_id
ORDER BY f1.field_name;


SET @sql=CONCAT('SELECT n.number, n.name, ',@sql,' FROM
  number AS n
        LEFT JOIN
    number_field AS nf1 ON n.id = nf1.number_id
        RIGHT JOIN
    field AS f1 ON f1.id = nf1.field_id
GROUP BY n.number,n.name
ORDER BY number');

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

FIDDLE