MySQL pivot tables - rows to colums . Query

Question

I have this table in mysql:

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

How can I get a result similar to this:

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

It is Possible in Mysql?

Answer 1

Create procedure

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

and use it.

See fiddle