MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sourcetable (`DATE` DATE, NAME VARCHAR(6), MARK INT);
INSERT INTO sourcetable VALUES
('2021-02-01',	'ALEX',	7),
('2021-02-01',	'JOHN',	5),
('2021-02-01',	'FRANK',	4),
('2021-02-02',	'EVA',	8),
('2021-02-02',	'ALICIA',	5);
SELECT * FROM sourcetable;

Records: 5  Duplicates: 0  Warnings: 0




DATE
NAME
MARK


2021-02-01
ALEX
7

2021-02-01
JOHN
5

2021-02-01
FRANK
4

2021-02-02
EVA
8

2021-02-02
ALICIA
5


CREATE PROCEDURE pivot (tablename VARCHAR(64),
                        groupname VARCHAR(64),
                        pivotname VARCHAR(64),
                        valuename  VARCHAR(64))
BEGIN
SELECT CONCAT('CREATE VIEW to_columnslist AS\n',
              'SELECT DISTINCT CONCAT(\'`\', `', pivotname,'`, \'` VARCHAR(255) path \\\'$."\', ', pivotname,', \'"\\\'\') line\n',
              'FROM ', tablename)
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT CONCAT(
'SELECT to_json.`', groupname,'`, parsed.*', '\n',
'FROM (SELECT `', groupname,'`, JSON_OBJECTAGG(`', pivotname,'`, `', valuename,'`) json_data', '\n',
'      FROM `', tablename,'`', '\n',
'      GROUP BY `', groupname,'`) to_json', '\n',
'CROSS JOIN JSON_TABLE( json_data,', '\n',
'                       "$" COLUMNS ( ', 
GROUP_CONCAT(line SEPARATOR ',\n                                     '),
' ) ) parsed'
) sql_text
INTO @sql
FROM to_columnslist;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
DROP VIEW to_columnslist;
END
CALL pivot ('sourcetable', 'DATE', 'NAME', 'MARK');


DATE
ALEX
JOHN
FRANK
EVA
ALICIA


2021-02-01
7
5
4
null
null

2021-02-02
null
null
null
8
5

DATE	NAME	MARK
2021-02-01	ALEX	7
2021-02-01	JOHN	5
2021-02-01	FRANK	4
2021-02-02	EVA	8
2021-02-02	ALICIA	5