oracle中如何进行行转列,Oracle中的行转列例子详解

--场景1：

A　　B

a1a2a3b4b5希望实现如下效果：

a1,2,3b4,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 fromdual;1.方法1：listagg--listagg() + group by: 推荐使用

select a,listagg(b,',') within group (order by b) as c from tmp group bya;--listagg() + over(partition by )

select distinct a,listagg(b,',') within group (order by b) over(partition by a) as c fromtmp ;2.wm_concatselect a,to_char(wm_concat(b)) as b from tmp group bya3.sys_connect_by_pathselect a, max(substr(sys_connect_by_path(b, ','), 2)) str

from (select a, b, row_number() over(partition by a order by b) rn fromtmp)

startwith rn = 1connectby rn = prior rn + 1

and a =prior agroup bya;4.max+decodeselecta,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 fromtmp)group byaorder by 1;5.row_number()+leadselect a, strbfrom (selecta,

row_number()over(partition by a order by b) asrn,

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

fromtmp

)where rn = 1

order by 1;6.model语句select a, substr(str,2) bfromtmp

modelreturn updated rows partition by(a) dimension by(row_number() over(partition by a order by b) asrn)

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 sex004 2

002 2

002 2

003 1

002 1希望实现如下效果：

c1 c2002 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 * fromtt;--2.SQL实现--存储过程动态拼接--(1)使用case

create or replace procedurerow_to_lineisstr_sqlvarchar2(4000);beginstr_sql := 'create or replace view v_row_to_line as select no';for x in (select distinct sex fromtt) loop

str_sql := str_sql || ',count(case when sex ='||x.sex||'then 1 else null end ) "'||x.sex||'"';endloop;

str_sql := str_sql || 'from tt group by no order by no';executeimmediate str_sql;end;/

--(2)使用decode

create or replace procedurerow_to_lineisstr_sqlvarchar2(4000);beginstr_sql := 'create or replace view v_row_to_line as select no';for x in (select distinct sex fromtt) loop

str_sql := str_sql || ',count(decode(sex,'||x.sex||', 1 , null)) "'||x.sex||'"';endloop;

str_sql := str_sql || 'from tt group by no order by no';executeimmediate str_sql;end;/SQL> execrow_to_line;

PL/SQL proceduresuccessfully completed

SQL> select * fromv_row_to_line;

NO1 2

---------------------------------------- ---------- ----------

002 1 2

003 1 0

004 0 1

--(3)动态传表名+列名

create or replace procedurerow_to_line

(

str_tabnamein varchar2,

str_col1in varchar2,

i_col2in varchar2)isstr_sqlvarchar2(4000);beginstr_sql := 'create or replace view v_row_to_line as select'||str_col1||' ';for x in (select distinct sex fromtt ) loop

str_sql := str_sql || ',count(decode('||i_col2||','||x.sex||', 1, null)) "'||x.sex||'"';endloop;

str_sql := str_sql || 'from'||str_tabname||'group by'||str_col1||'order by'||str_col1||' ';executeimmediate str_sql;end;--(4)使用游标

create or replace procedurerow_to_line

(

str_tabnamein varchar2,

str_col1in varchar2,

i_col2in varchar2,

cur_result out sys_refcursor

)isstr_sqlvarchar2(4000);beginstr_sql := 'select'||str_col1||' ';for x in (select distinct sex fromtt ) loop

str_sql := str_sql || ',count(decode('||i_col2||','||x.sex||', 1, null)) "'||x.sex||'"';endloop;

str_sql := str_sql || 'from'||str_tabname||'group by'||str_col1||'order by'||str_col1||' ';open cur_result forstr_sql;end;--(5).使用sql语句也可以解决

selectno,count(case sex when 1 then 1 else null end) c1,count(case sex when 2 then 1 else null end) c2fromttgroup bynoorder byno;