MariaDB数据透视表-行到列.查询

Question

我在MariaDB中有这个表:

<表类> 日期 名称 标记 tbody><<tr>2021-02-01亚历克斯72021-02-01约翰52021-02-01弗兰克42021-02-02EVA82021-02-02艾丽西娅5

创建程序

CREATE PROCEDURE pivot (tablename VARCHAR(64),
groupname VARCHAR(64),
pivotname VARCHAR(64),
valuename VARCHAR(64))
BEGIN
SELECT CONCAT('CREATE VIEW to_columnslist ASn',
'SELECT DISTINCT CONCAT('`', `', pivotname,'`, '` VARCHAR(255) path \'$."', ', pivotname,', '"\'') linen',
'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

并使用它

看到小提琴