Oracle中的行轉列例子詳解

--場景1：
A　　B
a　　1
a　　2
a　　3
b　　1
b　　2
b　　3

希望實現如下效果：
a  1,2,3
b  4,5

create table tmp as
select 'a' A, 1 B from dual union all
select 'a' A, 2 B from dual union all
select 'a' A, 3 B from dual union all
select 'b' A, 4 B from dual union all
select 'b' A, 5 B from dual;

1.方法1：listagg
--listagg() + group by: 推薦使用
select a,listagg(b,',')  within group (order by b) as c from tmp group by a;
--listagg() + over(partition by )
select distinct a,listagg(b,',')  within group (order by b) over(partition by a) as c from tmp ;

2.wm_concat
select a,to_char(wm_concat(b)) as b from tmp group by a

3.sys_connect_by_path
select a, max(substr(sys_connect_by_path(b, ','), 2)) str
  from (select a, b, row_number() over(partition by a order by b) rn from tmp)
 start with rn = 1
connect by rn = prior rn + 1
    and a = prior a
  group by a;
 
4.max+decode
select a,
       max(decode(rn, 1, b, null)) ||
       max(decode(rn, 2, ',' || b, null)) ||
       max(decode(rn, 3, ',' || b, null)) str
  from (select a,b,row_number() over(partition by a order by b) as rn from tmp) 
 group by a
 order by 1;

5.row_number()+lead
select a, str b
  from (select a,
               row_number() over(partition by a order by b) as rn,
               b || lead(',' || b, 1) over(partition by a order by b) ||
               lead(',' || b, 2) over(partition by a order by b) || 
               lead(',' || b, 3) over(partition by a order by b) as str
          from tmp
)
 where rn = 1
 order by 1;

6.model語句
select a, substr(str,2) b
  from tmp 
  model return updated rows partition by(a) dimension by(row_number()  over(partition by a order by b) as rn) 
  measures(cast(b as varchar2(20)) as str) 
  rules upsert iterate(3) until(presentv(str[iteration_number+2],1,0) = 0)
  (str [0] = str [0]||','||str[iteration_number + 1])
 order by 1;
 
--場景2： 
no  sex 
004 2 
002 2 
002 2 
003 1 
002 1 

希望實現如下效果：
      c1   c2 
002   1    2 
003   1    0 
004   0    1 

也就是說按no sex兩個欄位count人數，得到二維表。

--1.添加測試數據
create table tt(no varchar(20 char), sex number);
insert into tt values('004',2);
insert into tt values('002',2);
insert into tt values('002',2);
insert into tt values('003',1);
insert into tt values('002',1);
commit;
select * from tt;

--2.SQL實現
--存儲過程動態拼接
--(1)使用case
create or replace procedure row_to_line
is
    str_sql varchar2(4000);
begin
    str_sql := ' create or replace view v_row_to_line as select no ';
 
    for x in (select distinct sex from tt) loop
        str_sql := str_sql || ',count(case when sex = '||x.sex||' then 1 else null end ) "'||x.sex||'"';
    end loop;
 
    str_sql := str_sql || ' from tt group by no order by no ';
 
    execute immediate str_sql;
 
end;
/
 
--(2)使用decode
create or replace procedure row_to_line
is
    str_sql varchar2(4000);
begin
    str_sql := ' create or replace view v_row_to_line as select no ';
 
    for x in (select distinct sex from tt) loop
        str_sql := str_sql || ',count(decode(sex, '||x.sex||', 1 , null)) "'||x.sex||'"';
    end loop;
 
    str_sql := str_sql || ' from tt group by no order by no ';
 
    execute immediate str_sql;
 
end;
/
 
SQL> exec row_to_line;
  
PL/SQL procedure successfully completed
  
SQL> select * from v_row_to_line;
  
NO                                                1          2
---------------------------------------- ---------- ----------
002                                               1          2
003                                               1          0
004                                               0          1
 
--(3)動態傳表名+列名
create or replace procedure row_to_line
(
   str_tabname   in  varchar2,
   str_col1      in  varchar2,
   i_col2        in  varchar2
)
is
    str_sql varchar2(4000);
begin
    str_sql := ' create or replace view v_row_to_line as select '||str_col1||' ';
 
    for x in (select distinct sex from tt ) loop
        str_sql := str_sql || ',count(decode('||i_col2||', '||x.sex||', 1, null)) "'||x.sex||'"';
    end loop;
 
    str_sql := str_sql || ' from '||str_tabname||' group by '||str_col1||' order by '||str_col1||' ';
 
    execute immediate str_sql;
 
end;
 
--(4)使用游標
create or replace procedure row_to_line
(
   str_tabname   in  varchar2,
   str_col1      in  varchar2,
   i_col2        in  varchar2,
   cur_result    out sys_refcursor
)
is
    str_sql varchar2(4000);
begin
    str_sql := 'select '||str_col1||' ';
 
    for x in (select distinct sex from tt ) loop
        str_sql := str_sql || ',count(decode('||i_col2||', '||x.sex||', 1, null)) "'||x.sex||'"';
    end loop;
 
    str_sql := str_sql || ' from '||str_tabname||' group by '||str_col1||' order by '||str_col1||' ';
 
    open cur_result for str_sql;
 
end;
 
--(5).使用sql語句也可以解決
select no,
       count(case sex when 1 then 1 else null end) c1,
       count(case sex when 2 then 1 else null end) c2
  from tt
 group by no
 order by no;