By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.3.31-MariaDB |
CREATE TABLE Table1 (`pID` INT,`key_name` VARCHAR(20),`value` VARCHAR(20));
INSERT INTO Table1 VALUES
('10','series','Songs'),
('10','wood','Beech'),
('10','language','German'),
('11','series','Songs'),
('11','wood','Oak'),
('11','language','French'),
('12','series','Exams'),
('12','language','English');
SELECT
t1.pID,
t1.value,
t2.value,
t3.value
FROM Table1 t1
LEFT JOIN Table1 t2 on t2.pID=t1.pID and t2.key_name='wood'
LEFT JOIN Table1 t3 on t3.pID=t1.pID and t3.key_name='language'
WHERE t1.key_name='series';
| pID | value | value | value |
|---|---|---|---|
| 10 | Songs | Beech | German |
| 11 | Songs | Oak | French |
| 12 | Exams | null | English |