select distinct title,MAX(k1) 一科,max(k2) 二科,max(k3) 三科,max(k4) 四科 from(
select title,section,sys_connect_by_path(k1,',') k1,sys_connect_by_path(k2,',') k2,
sys_connect_by_path(k3,',') k3,sys_connect_by_path(k4,',') k4,level lev from
(select title,name,section,k1,k2,k3,k4,rownum prow,lag(rownum,1) over(partition by title order by name)
connect_id from
(select name,title,max(section) section,max(decode(section,'一科',name,null)) k1,
max(decode(section,'二科',name,null)) k2,
max(decode(section,'三科',name,null)) k3,
max(decode(section,'四科',name,null)) k4 from user_test group by name,title,section) r)
start with connect_id is null connect by prior prow=connect_id) group by title order by title desc
select title,section,sys_connect_by_path(k1,',') k1,sys_connect_by_path(k2,',') k2,
sys_connect_by_path(k3,',') k3,sys_connect_by_path(k4,',') k4,level lev from
(select title,name,section,k1,k2,k3,k4,rownum prow,lag(rownum,1) over(partition by title order by name)
connect_id from
(select name,title,max(section) section,max(decode(section,'一科',name,null)) k1,
max(decode(section,'二科',name,null)) k2,
max(decode(section,'三科',name,null)) k3,
max(decode(section,'四科',name,null)) k4 from user_test group by name,title,section) r)
start with connect_id is null connect by prior prow=connect_id) group by title order by title desc
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成,这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
SQL> select distinct title,MAX(k1) 一科,max(k2) 二科,max(k3) 三科,max(k4) 四科 from(
2 select title,section,sys_connect_by_path(k1,',') k1,sys_connect_by_path(k2,',') k2,
3 sys_connect_by_path(k3,',') k3,sys_connect_by_path(k4,',') k4,level lev from
4 (select title,name,section,k1,k2,k3,k4,rownum prow,lag(rownum,1) over(partition by title order by name)
5 connect_id from
6 (select name,title,max(section) section,max(decode(section,'一科',name,null)) k1,
7 max(decode(section,'二科',name,null)) k2,
8 max(decode(section,'三科',name,null)) k3,
9 max(decode(section,'四科',name,null)) k4 from user_test group by name,title,section) r)
10 start with connect_id is null connect by prior prow=connect_id) group by title order by title desc
11 ;TITLE 一科 二科 三科 四科
---------- -------------------- --------------- --------------- ---------------
科长 ,3232,,掌三,找刘 ,,里斯,, ,,,, ,,,,
股长 , , ,网无 ,
副科 ,ada,ded,,阿谔谔 ,,,啊啊, ,,,, ,,,,
队长 ,大大 , , ,SQL> select * from user_test;NAME TITLE SECTION
-------------------- ---------- --------------------
掌三 科长 一科
里斯 科长 二科
网无 股长 三科
找刘 科长 一科
啊啊 副科 二科
阿谔谔 副科 一科
ada 副科 一科
ded 副科 一科
3232 科长 一科
大大 队长 一科10 rows selectedSQL>
SQL> declare
2 cursor v_cursor is select id,x2 from b;
3 v_output varchar2(50);
4 begin
5 for v_tempcursor in v_cursor loop
6 v_output :=v_output || v_tempcursor.x2;
7 end loop;
8 dbms_output.put_line(v_output);
9 end;
----------
方法一:
create or replace function get_id(p_name in varchar2) return varchar2 is
Result varchar(100);
cursor rad_cursor(v_name varchar2) is
select 厂家ID from table
where 产品名称=v_name;
--rad_val rad_cursor%ROWTYPE;
begin
Result:=;
for ral_val in rad_cursor(p_name)
loop
Result:=Result || ',' || rad_val.厂家ID;
end loop;
return(Result);
end get_id;
---------------------------------
方法二:
create or replace function get_book(p_id in varchar2) return varchar2 is
Result varchar2(100);
cursor rad_cursor(v_id varchar2) is
select distinct book from student
where id=v_id ;
v_count Number;
begin
v_count:=0;
Result:='';
for v_sor in rad_cursor(p_id) loop
if v_count=0 then
Result:=v_sor.book;
v_count:=v_count+1;
else
Result:=Result||','||v_sor.book;
end if;
end loop;
return(Result);
end get_book;
/