--场景1：

A　　B

a　　1

a　　2

a　　3

b　　4

b　　5

希望实现如下效果：

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[] = str[]||','||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('',2);

insert into tt values('',2);

insert into tt values('',2);

insert into tt values('',1);

insert into tt values('',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;