原表:字段1 字段2
-------------
A A
A B
A C
A D
B E
B F
C G
C H
C I
-------------结果表:字段1 字段2 字段3 字段4 字段5
---------------------------------
A A B C D
B E F NULL NULL
C G H I NULL
-------------
A A
A B
A C
A D
B E
B F
C G
C H
C I
-------------结果表:字段1 字段2 字段3 字段4 字段5
---------------------------------
A A B C D
B E F NULL NULL
C G H I NULL
with cte as(
select 'A' col1 , 'A' col2 from dual union all
select 'A' ,'B' from dual union all
select 'A', 'C' from dual union all
select 'A', 'D' from dual union all
select 'B', 'E' from dual union all
select 'B', 'F' from dual union all
select 'C', 'G' from dual union all
select 'C', 'H' from dual union all
select 'C', 'I' from dual)
select col1,
max(case rn when 1 then col2 end) col2,
max(case rn when 2 then col2 end) col3,
max(case rn when 3 then col2 end) col4,
max(case rn when 4 then col2 end) col5
from (
select col1,col2,row_number()over(partition by col1 order by col2) as rn from cte
) k
group by col1;
/*
C C C C C
- - - - -
A A B C D
B E F
C G H I*/
select 'A' col1 , 'A' col2 from dual union all
select 'A' ,'B' from dual union all
select 'A', 'C' from dual union all
select 'A', 'D' from dual union all
select 'B', 'E' from dual union all
select 'B', 'F' from dual union all
select 'C', 'G' from dual union all
select 'C', 'H' from dual union all
select 'C', 'I' from dual)--1楼到这里为提供数据的,相当于你的表
--以下为你要的语句
select col1,
max(case rn when 1 then col2 end) col2,
max(case rn when 2 then col2 end) col3,
max(case rn when 3 then col2 end) col4,
max(case rn when 4 then col2 end) col5
from (
select col1,col2,row_number()over(partition by col1 order by col2) as rn from cte
) k
group by col1;
create table test as(
select 'A' col1 , 'A' col2 from dual union all
select 'A' ,'B' from dual union all
select 'A', 'C' from dual union all
select 'A', 'D' from dual union all
select 'B', 'E' from dual union all
select 'B', 'F' from dual union all
select 'C', 'G' from dual union all
select 'C', 'H' from dual union all
select 'C', 'I' from dual); declare
v_max number ;
v_var varchar2(2000);
begin
select max(rn)
into v_max
from
(select col1,col2,row_number()over(partition by col1 order by col2) as rn from test) k;
for i in 1..v_max loop
v_var :=v_var||','||'max( case rn when '||i||' then col2 end) col'||(i+1);
end loop;
v_var :='select col1 '||v_var||' from (
select col1,col2,row_number()over(partition by col1 order by col2) as rn from test ) k
group by col1';
dbms_output.put_line(v_var); ----这个就是你要的语句啦!
execute immediate v_var ; ----不知道有没方法显示出来 。。呵呵
end;
--这里有个办法就是把显示出来的v_var 复制 然后执行
select 'A' col1 , 'A' col2 from dual union all
select 'A' ,'B' from dual union all
select 'A', 'C' from dual union all
select 'A', 'D' from dual union all
select 'B', 'E' from dual union all
select 'B', 'F' from dual union all
select 'C', 'G' from dual union all
select 'C', 'H' from dual union all
select 'C', 'I' from dual); declare
v_max number ;
v_var varchar2(2000);
begin
select max(rn)
into v_max
from
(select col1,col2,row_number()over(partition by col1 order by col2) as rn from test) k;
for i in 1..v_max loop
v_var :=v_var||','||'max( case rn when '||i||' then col2 end) col'||(i+1);
end loop;
v_var :='select col1 '||v_var||' from (
select col1,col2,row_number()over(partition by col1 order by col2) as rn from test ) k
group by col1';
dbms_output.put_line(v_var); ----这个就是你要的语句啦!
execute immediate v_var ; ----不知道有没方法显示出来 。。呵呵
end;
--这里有个办法就是把显示出来的v_var 复制 然后执行