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

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