By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab1 (
`pID` INTEGER,
`key_name` VARCHAR(10),
`value` VARCHAR(9)
);
INSERT INTO tab1
(`pID`, `key_name`, `value`)
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');
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(CASE WHEN `key_name` = ''',
`key_name`,
''' THEN `value` ELSe "" END) AS `',
`key_name`, '`'
)
) INTO @sql
FROM `tab1` ;
SET @sql = CONCAT('SELECT `pID`, ',@sql,' from tab1
GROUP BY `pID`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
| pID | language | series | wood |
|---|---|---|---|
| 10 | German | Songs | Beech |
| 11 | French | Songs | Oak |
| 12 | English | Exams |