Use row values from another table to select them as columns and establish relations between them (pivot table)

Question

There's a following table, called fields:

And there's a dedicated table to store its values, called values

I want to run a query to produce the following output:

Finished | Faculity  | Characteristic | Photo
---------------------------------------------
   1     |    Math   |        Good    |     
   0     |  Biology  |     Not Good   |          

I want to build a query that outputs aformentioned result. But it's not that easy as it seems. From this simlar question, I have tried running the following query:

SELECT flds.id,
  (case when flds.name = 'Finished' THEN vals.value END) AS Finished,
  (case when flds.name = 'Faculty' THEN vals.value END) AS Faculty,
  (case when flds.name = 'Characteristic' THEN vals.value END) AS Characteristic,
  (case when flds.name = 'Photo' THEN vals.value END) AS Photo
  
FROM `values` vals

LEFT JOIN `fields` flds
  ON vals.field_id = flds.id
  
GROUP BY
    flds.id,
    vals.value;

Which gives me an unexpected result:

Is there any way to resolve it?

Answer 1

count the number of field values prior to the current id then group by and aggregate eg

select rn,
       max(case when name = 'Finished' then value else null end) 'Finished',
       max(case when name = 'faculty' then value else null end) 'faculty',
       max(case when name = 'Characteristic' then value else null end) 'Characteristic',
       max(case when name = 'Photo' then value else null end) 'Photo'
from
(
select f.name,fv.value,(select count(*) from field_values fv1 where fv1.field_id = fv.field_id and fv1.id < fv.id)  rn
from field_values fv
join fields f on f.id = fv.field_id
) s
group by rn