MySQL动态行转列的实现方法

一、不使用存储过程的MySQL动态行转列

MySQL动态行转列是将行数据转换为列数据的过程，将一个或多个字段的值作为列名，对于每个值都新建一列，然后将行数据中对应的值插入到相应的列中。以下是不使用存储过程的实现方法：

SELECT
  id,
  MAX(CASE name WHEN 'AAA' THEN value ELSE NULL END) AS AAA,
  MAX(CASE name WHEN 'BBB' THEN value ELSE NULL END) AS BBB,
  MAX(CASE name WHEN 'CCC' THEN value ELSE NULL END) AS CCC
FROM
  table_name
GROUP BY
  id;

其中，id 是行标识符，name是原表中的字段名，value是要转换的字段值。以上就是一个简单的 MySQL 动态行转列实现的例子。

二、MySQL动态行转列SQL

除了上面的实现方法外，我们也可以使用动态 SQL 的实现方式。这种方式需要先用 SELECT 语句查询出所有的 name 值，并将它们 CONCAT 到字符串中。接着，我们就能生成一个包含所有列名的字符串用于后续的 SQL 语句中。以下示例代码展示了如何使用 SQL 实现动态行转列：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN name = ''', name, ''' THEN value END) AS ', name)
  ) INTO @sql
FROM
  table_name;

SET @sql = CONCAT('SELECT id, ', @sql, ' FROM table_name GROUP BY id');

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

三、MySQL动态列名

使用 MySQL 动态列名的方法可以达到效果相同，具有较好的可维护性。在 MySQL 中可以通过“information_schema”系统表获得动态列名。以下是演示代码：

SET @sql := (
  SELECT
    CONCAT(
      'SELECT id',
      GROUP_CONCAT(
        DISTINCT CONCAT(',MAX(IF(name="', name, '", value, NULL)) AS `', name, '`')
      )
      ' FROM table_name GROUP BY id'
    ) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'table_name' AND TABLE_SCHEMA = 'database_name' AND COLUMN_NAME NOT IN ('id', 'name')
  );

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

四、MySQL动态行转列的经典实现

除了上述的方法外，我们还有一种经典实现方法，即使用 GROUP_CONCAT 构建 SQL 语句并使用 PREPARE/EXECUTE 进行执行。

SET @sql = (SELECT
              GROUP_CONCAT(
                CONCAT('MAX(IF(`name` = "', `name`, '", `value`, "")) AS `', `name`, '`')
              )
              FROM (
                SELECT DISTINCT `name` FROM `table_name`
              ) AS `sub`
            );

SET @sql = CONCAT('SELECT `id`, ', @sql, ' FROM `table_name` GROUP BY `id`');

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

五、MySQL行转列SQL

MySQL 行转列是行数据转换为列数据的过程。转换后的数据非常适合 Excel 的二维表数据表示，常用于报表、统计等场景中。以下是 MySQL 行转列 SQL 语句的示例：

SELECT
  id,
  CASE WHEN name = 'AAA' THEN value END AS AAA,
  CASE WHEN name = 'BBB' THEN value END AS BBB,
  CASE WHEN name = 'CCC' THEN value END AS CCC
FROM
  table_name;

六、MySQL动态行转列函数

除了上述方法外，我们还可以使用动态行转列函数。函数可以在复杂的数据转换场景下减少代码量。以下是 MySQL 动态行转列函数的示例：

DELIMITER #
CREATE FUNCTION dynamic_pivot (IN statement VARCHAR(64000))
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE pivot_query TEXT DEFAULT '';
    SELECT GROUP_CONCAT(DISTINCT
      CONCAT(
        'max(if(`name` = ''', `name`, ''', `value`, null)) as ', `name`
      )
    )
    INTO @pivot_query
    FROM (SELECT `name` FROM table_name GROUP BY `name`) AS sub;

    SET pivot_query = CONCAT(
      'SELECT `id`, ', @pivot_query, ' FROM table_name GROUP BY `id`'
    );
    SET @query = CONCAT(
      'SELECT GROUP_CONCAT(DISTINCT CONCAT(`', pivot_query, '`)) AS `pivot` FROM (', pivot_query, ') `base_query`'
    );
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT
      GROUP_CONCAT(
        DISTINCT CONCAT(
          'CASE WHEN `name` = ''', `name`, ''' THEN `value` END AS `', `name`, '`'
        )
      )
    INTO 
      @dynamic_query
    FROM
      (SELECT `name` FROM table_name GROUP BY `name`) AS `sub`;

    SET @dynamic_query = CONCAT(
      'SELECT `id`,', @dynamic_query, ' FROM table_name GROUP BY `id`'
    );

    SELECT concat('{ "pivot": "', pivot, '", "data": [',group_concat(row SEPARATOR ','),']}')
    INTO @result
    FROM
      (SELECT piv.*, @dynamic_query AS TO_ROW
        FROM
        (SELECT @query AS TO_PIVOT) t
        CROSS JOIN
        (SELECT @dynamic_query) r
        JOIN
      (
        SELECT `id`,
          GROUP_CONCAT(
            CONCAT('MAX(IF(`name` = "', `name`, '", `value`, NULL))')
          ) AS to_select FROM `table_name` GROUP BY `id`
      ) f ON 1=1
      JOIN (SELECT @rownum:=0) ii
      JOIN (
        SELECT @rownum:=@rownum+1 AS row, al.* 
        FROM (SELECT * FROM table_name ORDER BY `id`, `name`) al
      ) piv ON f.to_select LIKE CONCAT('%', `piv`.`name`, '%')
    ) all_queries;


    RETURN SUBSTRING(@result,2,LENGTH(@result)-2);

END#
DELIMITER ;

七、MySQL行转列经典实现

我们还可以使用经典实现方法来进行 MySQL 行转列。在经典实现方法中，我们使用 CASE WHEN 条件语句将单个行转换为多个列。以下是 MySQL 行转列经典实现的示例：

SELECT
  id,
  MAX(CASE name WHEN 'AAA' THEN value ELSE NULL END) AS AAA,
  MAX(CASE name WHEN 'BBB' THEN value ELSE NULL END) AS BBB,
  MAX(CASE name WHEN 'CCC' THEN value ELSE NULL END) AS CCC
FROM
  table_name
GROUP BY
  id;

八、MySQL怎么行转列

MySQL 行转列是一种将行数据转换为列数据的过程。它支持多种实现方法，包括使用 SQL 语句、使用动态 SQL、使用存储过程等等。我们可以根据实际情况选择适合自己的方法来实现行转列。以上的方法均可经过改造实现行转列。

九、MySQL动态列转行

除了行转列，我们还可以使用 MySQL 动态列转行。该方法可以将一种表示形式转变为另一种表示形式，将列转换为行。以下是 MySQL 动态列转行的 SQL 语句示例：

SELECT `id`, CONCAT_WS(', ', COALESCE(`AAA`, NULL), COALESCE(`BBB`, NULL), COALESCE(`CCC`, NULL)) AS `values` FROM `table_name`;

十、SQL行转列三种方式

MySQL 行转列是将行数据转换为列数据的过程。常用于报表、统计等场景中。除了 MySQL 之外，还有其他数据库也支持行转列功能。以下是 SQL 行转列实现的三种方便方式：

• 使用 SQL CASE WHEN 语句转换行为列。
• 使用 SQL PIVOT 语句转换行为列。
• 使用 SQL 特有的行转列函数。

以上三种方式均非常简单、易于使用。