mysql实现列转行和行转列方式_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
        ) 
;