oracle 行转列&列转行

1、行转列：

原始数据：

方法1：case when拼接简单粗暴（同理decode应该也行）

select t.id,
       t.name,
       sum(case
         when t.subject = '语文' then
          t.grade
       end) as 语文,
       sum(case
         when t.subject = '数学' then
          t.grade
       end) as 数学,
       sum(case
         when t.subject = '英语' then
          t.grade
       end) as 英语
  from test1 t
  group by t.id,
       t.name

方案2：调用pivot函数

select *
  from test1 t
pivot (sum(grade) for subject in('语文','数学','英语'));

（试完发现生成的字段名有个’，这里可以给个别名）

select *
  from test1 t
pivot (sum(grade) for subject in('语文' as 语文,'数学' as 数学,'英语' as 英语));

（如果字段不确定的话，可以用动态sql拼接）

declare
  icol varchar2(2000);
  scol varchar2(2000);
  ssql varchar2(2000);

begin
  select listagg(subject, ',') within group(order by subject)
    into scol
    from (select distinct '''' || subject || '''' as subject from test1);

  icol := 'ID,NAME,' || replace(scol,'''','');

  ssql := 'insert into test2( ' || icol || ' )
           select *
             from test1 t
            pivot (sum(grade) for subject in(' || scol || '))';

  dbms_output.put_line(ssql);
  execute immediate ssql;
  commit;
end;

拼接的ssql长这样：

结果：

2、列转行

原始数据：

方法1：union all简单粗暴

select t.id,t.name,'语文' as subject,t.语文 as grade
from test2 t
union all
select t.id,t.name,'数学' as subject,t.数学 as grade
from test2 t
union all
select t.id,t.name,'英语' as subject,t.英语 as grade
from test2 t

方法2：调用unpivot函数

select *
  from test2 t unpivot(grade for subject in(语文 as '语文',
                                            数学 as '数学',
                                            英语 as '英语'))