如何动态的将mysql行转列

Question

问题遇到的现象和发生背景

我希望上图中选中的部分是动态的(只能使用sql，不能使用其他的计算机语言)。

INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (1, '2022-04-22 16:44:42', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (2, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (3, '2022-04-23 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (4, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (5, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (6, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (7, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (8, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (9, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (10, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (11, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (12, '2022-04-24 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (13, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (14, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (15, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (16, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (17, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (18, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (19, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (20, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (21, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (22, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (23, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (24, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (25, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (26, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (27, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (28, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (29, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (30, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (31, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (32, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (33, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (34, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (35, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (36, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (37, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (38, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (39, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (40, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (41, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (42, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (43, '2022-04-23 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (44, '2022-04-24 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (45, '2022-04-21 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (46, '2022-04-22 16:45:28', 'd');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (47, '2022-04-23 16:45:28', 'c');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (48, '2022-04-24 16:45:28', 'b');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (49, '2022-04-21 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (50, '2022-04-22 16:45:28', 'a');
INSERT INTO `test` (`id`, `created_at`, `key`) VALUES (51, '2022-04-23 16:45:28', 'b');

我想要达到的结果

只用用sql来实现，不知道是否有方法达到，求个思路

Answer 1

纯select的sql是不能实现动态列的，sql必须在查询发出前就已经确定好有哪些列，包括列名和数据类型，这与sql解析器本身的逻辑有关。
就算是其他数据库中的pivot或者unpivot的写法，也都是要指定列（目前真正唯一支持动态列的可能只有oracle18c的多态表函数功能）。
对于一般的数据库，可以通过在存储过程中拼接动态sql字符串来执行，你可以搜一下 “动态sql”去了解一下，这也是在数据库里执行的，没有使用到其他开发语言。