不定列行列转换 如 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;即可
create function get_str(p_id in varchar2) return varchar2 as num number:=0 str varchar2(50); cursor t_sor is select leader from xtable id=p_id; begin for v_sor in t_sor loop if num=0 then str:=v_sor.leader; else str:=str||','||v_sor.leader; end if; num:=num+1; end loop; return str; end; / select id,get_str(id) from xtable group by id
declare p_ID varchar(10); p_Leader varchar(50)=''; cursor aa is select id,leader from xtable where id='011' begin for n in aa loop p_leader=p_leader||n.leader||','; end loop; delete xtable where id='001' insert into xtable values('001',p_leader); end;
如
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;即可
return varchar2
as
num number:=0
str varchar2(50);
cursor t_sor is
select leader from xtable id=p_id;
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.leader;
else
str:=str||','||v_sor.leader;
end if;
num:=num+1;
end loop;
return str;
end;
/
select id,get_str(id) from xtable group by id
p_ID varchar(10);
p_Leader varchar(50)='';
cursor aa is
select id,leader
from xtable
where id='011'
begin
for n in aa loop
p_leader=p_leader||n.leader||',';
end loop;
delete xtable where id='001'
insert into xtable values('001',p_leader);
end;