0

I have this table in MariaDB:

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 MariaDB?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Alessandro La Ragione
  • 1
  • 3
  • Use dynamic SQL in stored procedure. – Akina Feb 10 '21 at 10:38
  • 2
    That's a sql anti-pattern. While rules are made to be broken, in principle it indicates you're trying to do something the wrong way, or in the wrong place (do this in presentation layers, not in data layers) – MatBailie Feb 10 '21 at 10:41
  • 1
    Seriously consider handling issues of data display in application code – Strawberry Feb 10 '21 at 14:11

1 Answers1

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

Akina
  • 39,301
  • 5
  • 14
  • 25