mysql 实现列转行和行转列

1、行转列(将多行数据转为单行多列)

1.1、使用 CASE WHEN + 聚合函数

SELECT 
    id,
    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS '数学',
    MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS '语文',
    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS '英语'
FROM student_scores
GROUP BY id;

1.2、使用 IF + 聚合函数

SELECT 
    id,
    MAX(IF(subject = '数学', score, NULL)) AS '数学',
    MAX(IF(subject = '语文', score, NULL)) AS '语文',
    MAX(IF(subject = '英语', score, NULL)) AS '英语'
FROM student_scores
GROUP BY id;

1.3、使用 PIVOT (MySQL 8.0+)

SELECT 
    id,
    JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.数学')) AS '数学',
    JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.语文')) AS '语文',
    JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.英语')) AS '英语'
FROM (
    SELECT 
        id,
        JSON_OBJECTAGG(subject, score) AS pivot_data
    FROM student_scores
    GROUP BY id
) AS t;

1.4、dataworks使用wm_concat函数和keyvalue

• 缺点：当字符串存在英文冒号时会导致获取的值为空；中文冒号不受影响
• 如果存在重复的数据，将导致取数时随机取其中一个；核心原因为wm_concat函数在拼接时顺序不固定，哪怕是增加了order by也没有用
• keyvalue从字符串中取值时，如果有重复key，从左到右取第一个key的值

select  id
        ,keyvalue(column_value,'name') as name
        ,keyvalue(column_value,'age') as age
from    (
            select  id
                    ,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
            from    school
            group by id
        ) 
;

-- 如果值存在英文冒号，导致取值为空的原因，看下面两个sql例子即可理解
-- 返回null
select keyvalue('name:小红:3737;age:13','name');
-- 返回3737
select keyvalue('name:小红:3737;age:13','name:小红');

2、列转行(将多列数据转为多行)

2.1、使用 UNION ALL

SELECT id, '数学' AS subject, 数学 AS score FROM student_scores_pivot
UNION ALL
SELECT id, '语文' AS subject, 语文 AS score FROM student_scores_pivot
UNION ALL
SELECT id, '英语' AS subject, 英语 AS score FROM student_scores_pivot
ORDER BY id, subject;

2.2、使用 CROSS JOIN + 条件筛选

• 优点是不用频繁读取磁盘

SELECT 
    s.id,
    c.subject,
    CASE c.subject
        WHEN '数学' THEN s.数学
        WHEN '语文' THEN s.语文
        WHEN '英语' THEN s.英语
    END AS score
FROM student_scores_pivot s
CROSS JOIN (
    SELECT '数学' AS subject UNION ALL
    SELECT '语文' UNION ALL
    SELECT '英语'
) c;

• 同样的语句，使用values和row

SELECT 
    s.id,
    c.subject,
    CASE c.subject
        WHEN '数学' THEN s.数学
        WHEN '语文' THEN s.语文
        WHEN '英语' THEN s.英语
    END AS score
FROM student_scores_pivot s
CROSS JOIN (
	values
    row('数学') 
    ,row('语文')
    ,row('英语')
) c(subject);

2.3、使用 JSON 函数 (MySQL 8.0+)

SELECT 
    id,
    jt.subject,
    jt.score
FROM student_scores_pivot,
JSON_TABLE(
    JSON_OBJECT(
        '数学', 数学,
        '语文', 语文,
        '英语', 英语
    ),
    '$.*' COLUMNS(
        subject VARCHAR(10) PATH '$.key',
        score INT PATH '$.value'
    )
) AS jt;

3、动态行转列

• 对于不确定列名的情况，可以使用存储过程动态生成SQL：

DELIMITER //
CREATE PROCEDURE dynamic_pivot(IN table_name VARCHAR(100), IN row_id VARCHAR(100), IN pivot_col VARCHAR(100), IN value_col VARCHAR(100))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(100);
    DECLARE col_list TEXT DEFAULT '';
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT pivot_col FROM table_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET col_list = CONCAT(col_list, 
            IF(col_list = '', '', ', '), 
            'MAX(CASE WHEN ', pivot_col, ' = ''', col_name, ''' THEN ', value_col, ' ELSE NULL END) AS `', col_name, '`');
    END LOOP;
    CLOSE cur;
    
    SET @sql = CONCAT('SELECT ', row_id, ', ', col_list, ' FROM ', table_name, ' GROUP BY ', row_id, ';');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程
CALL dynamic_pivot('student_scores', 'id', 'subject', 'score');

4、详细测试demo

4.1、dataworks使用wm_concat函数和keyvalue实现行转列

-- 创建表
create table if not exists school (
`id` string,
`obj_name` string,
`obj_value` string
);

-- 插入测试数据
insert into school
values 
('1','name','小明'),
('1','age','12'),
('2','name','小红'),
('2','age','13')
;


-- 列转行
select  id
        ,keyvalue(column_value,'name') as name
        ,keyvalue(column_value,'age') as age
from    (
            select  id
                    ,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
            from    school
            group by id
        ) 
;