如何在 MySQL 中实现动态行转列

有时，您的数据可能存储在行中，您可能希望将其报告为列。在这种情况下，您需要将行转换为列。有时，甚至这些行也可能是可变的。所以你可能知道你需要多少列。在这种情况下，您需要动态地将行转置为列。由于 MySQL 中没有内置函数来执行此操作，因此您需要使用 SQL 查询来完成它。这是一个 SQL 查询，用于将行动态转换为 MySQL 中的列。
原文地址：https://ubiq.co/database-blog/transpose-rows-columns-dynamically-mysql/

CREATE TABLE Meeting
(
    ID INT,
    Meeting_id INT,
    field_key VARCHAR(100),
    field_value VARCHAR(100)
);

INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (1, 1,'first_name' , 'Alec');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (2, 1,'last_name' , 'Jones');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (3, 1,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (4,2,'first_name' , 'John');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (5,2,'last_name' , 'Doe');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (6,2,'occupation' , 'engineer');

+------+------------+------------+-------------+
| ID   | Meeting_id | field_key  | field_value |
+------+------------+------------+-------------+
|    1 |          1 | first_name | Alec        |
|    2 |          1 | last_name  | Jones       |
|    3 |          1 | occupation | engineer    |
|    4 |          2 | first_name | John        |
|    5 |          2 | last_name  | Doe         |
|    6 |          2 | occupation | engineer    |
+------+------------+------------+-------------+

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when field_key = ''',
      field_key,
      ''' then field_value end) ',
      field_key
    )
  ) INTO @sql
FROM
  Meeting;
SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' 
                  FROM Meeting 
                   GROUP BY Meeting_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;