MySQL 动态将行转换为列

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

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

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

CREATE PROCEDURE pivot_table(
  IN table_name VARCHAR(255),
  IN row_column VARCHAR(255),
  IN pivot_column VARCHAR(255),
  IN value_column VARCHAR(255)
)
BEGIN
    SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN ', pivot_column, ' = ''',
               pivot_value,
               ''' THEN ', value_column, ' END) AS ',
               pivot_value)
      ) INTO @sql
    FROM table_name;

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

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

CALL pivot_table('table_name', 'row_column', 'pivot_column', 'value_column');