with t1 as ( select 1 f1, A1 f2 from dual union all select 1 f1, B1 f2 from dual ) select f1, max(case when f2='A'||f1 then f2 else null end ) f2A, max(case when f2='B'||f1 then f2 else null end ) f2B, max(case when f2='C'||f1 then f2 else null end ) f2C from t1 group by f1;
SQL> with tt as( 2 select 1 id,'A1' name from dual 3 union all select 2,'A2' from dual 4 union all select 3,'A3' from dual 5 union all select 1,'B1' from dual 6 union all select 2,' ' from dual 7 union all select 3,'B3' from dual 8 union all select 1,' ' from dual 9 union all select 2,'C2' from dual 10 union all select 3,'C3' from dual 11 ) 12 select id, 13 nvl(max(decode(substr(name,0,1),'A',name)),'null') A, 14 nvl(max(decode(substr(name,0,1),'B',name)),'null') B, 15 nvl(max(decode(substr(name,0,1),'C',name)),'null') C 16 from tt 17 group by id;
ID A B C ---------- ---- ---- ---- 1 A1 B1 null 2 A2 null C2 3 A3 B3 C3
你的三个问题,好像是这个结果可以解决,用这个试下,改改应该可以了 create or replace procedure row_col( v_cirsor out sys_refcursor ) is sqlstr varchar2(4000); cursor mycursor is select ', sum(decode(car_id,'||car_id||',measure_value,0))'||'"'||car_id||'" ' c2 from 表 group by car_id; r1 mycursor%rowtype; begin sqlstr :='select size_id '; open mycursor; loop fetch mycursor into r1; exit when mycursor%notfound; sqlstr:=sqlstr || r1.c2; end loop; close mycursor; sqlstr:=sqlstr||' from result3 group by size_id order by size_id '; open v_cirsor for sqlstr; end row_col;
(
select 1 f1, A1 f2 from dual
union all
select 1 f1, B1 f2 from dual
)
select f1,
max(case when f2='A'||f1 then f2 else null end ) f2A,
max(case when f2='B'||f1 then f2 else null end ) f2B,
max(case when f2='C'||f1 then f2 else null end ) f2C
from t1
group by f1;
2 select 1 id,'A1' name from dual
3 union all select 2,'A2' from dual
4 union all select 3,'A3' from dual
5 union all select 1,'B1' from dual
6 union all select 2,' ' from dual
7 union all select 3,'B3' from dual
8 union all select 1,' ' from dual
9 union all select 2,'C2' from dual
10 union all select 3,'C3' from dual
11 )
12 select id,
13 nvl(max(decode(substr(name,0,1),'A',name)),'null') A,
14 nvl(max(decode(substr(name,0,1),'B',name)),'null') B,
15 nvl(max(decode(substr(name,0,1),'C',name)),'null') C
16 from tt
17 group by id;
ID A B C
---------- ---- ---- ----
1 A1 B1 null
2 A2 null C2
3 A3 B3 C3
http://www.cnblogs.com/gkl0818/archive/2009/02/25/1398078.html
create or replace procedure row_col(
v_cirsor out sys_refcursor
) is
sqlstr varchar2(4000);
cursor mycursor is select ', sum(decode(car_id,'||car_id||',measure_value,0))'||'"'||car_id||'" ' c2 from 表 group by car_id;
r1 mycursor%rowtype;
begin
sqlstr :='select size_id ';
open mycursor;
loop
fetch mycursor into r1;
exit when mycursor%notfound;
sqlstr:=sqlstr || r1.c2;
end loop;
close mycursor;
sqlstr:=sqlstr||' from result3 group by size_id order by size_id ';
open v_cirsor for sqlstr;
end row_col;