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

Answer 2

Assuming that there is equal number of rows in field_values for each id in fields. That is to say that this query returns a 1:

SELECT CASE 
        WHEN EXISTS(
                SELECT 1
                FROM(
                    SELECT COUNT(*) AS cnt
                    FROM field_values
                    GROUP BY field_id
                    ) subq
                HAVING MAX(subq.cnt) <> MIN(subq.cnt)
                )
            THEN 0
            ELSE 1
        END AS test;

Otherwise, the solution will require a bunch of FULL JOINS and it will get messy. Especially since MySQL doesn't have a FULL JOIN, so you'd have to UNION ALL a LEFT/RIGHT ANTI JOIN with a RIGHT/LEFT JOIN (respectively).

Create the tables an populate with your sample data

-- Create your tables
DROP TABLE IF EXISTS fields;
CREATE TABLE fields (id BIGINT NOT NULL
                    , name VARCHAR(100) NOT NULL
                    );

INSERT INTO fields(id
                , name
                )
VALUES(19, 'Finished')
    , (20, 'Faculty')
    , (21, 'Characteristic')
    , (27, 'Photo');
    
DROP TABLE IF EXISTS field_values;
CREATE TABLE field_values(id BIGINT NOT NULL
                        , field_id BIGINT NOT NULL
                        , `value` VARCHAR(100)
                        );

INSERT INTO field_values(id
                , field_id
                , `value` 
                )
VALUES(1, 20, 'Math')
    , (2, 21, 'Good')
    , (3, 19, '1')
    , (4, 27, NULL)
    , (5, 20, 'Biology')
    , (6, 21, 'Not good')
    , (7, 19, '0')
    , (8, 27, NULL);

JOIN fields with field_values, keeping track of the order of the id from field_values. As I am also assuming that you want to use this value to determine the final display order.

JOIN this result with itself for as many columns as you want in your final result, matching on the appropriate ordinal position.

WITH base
AS
(
    SELECT fv.id
        , fv.`value`
        , f.name
        , ROW_NUMBER() OVER(PARTITION BY f.id ORDER BY fv.id ASC) AS rn
    FROM fields f
    INNER JOIN field_values fv
        ON f.id = fv.field_id
)

SELECT col1.`value` AS Finished
    , col2.`value` AS Faculty
    , col3.`value` AS Characteristic
    , col4.`value` AS Photo
FROM(
    SELECT `value`
        , rn
    FROM base
    WHERE name = 'Finished'
    ) col1
INNER JOIN (
    SELECT `value`
        , rn
    FROM base
    WHERE name = 'Faculty'
    ) col2
    ON col1.rn = col2.rn
INNER JOIN (
    SELECT `value`
        , rn
    FROM base
    WHERE name = 'Characteristic'
    ) col3
    ON col2.rn = col3.rn
INNER JOIN (
    SELECT `value`
        , rn
    FROM base
    WHERE name = 'Photo'
    ) col4
    ON col3.rn = col4.rn;

Try it yourself: db<>fiddle