--场景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;