又是这个问题。不定列行列转换 如 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语句是无法实现了
create function get_str(p_id in varchar2) return varchar2 as num number:=0 str varchar2(50); cursor t_sor is select name from id2=p_id; begin for v_sor in t_sor loop if num=0 then str:=v_sor.name; else str:=str||'#'||v_sor.name; end if; num:=num+1; end loop; return str; end; / select id,get_str(id) name from a;
select nodeid,parentnodeid,id,name,sys_connect_by_path(name,'#') from ( select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from (select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid from a,b where a.id = b.id2 ) union ( select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual ) ) idtree connect by prior nodeid = parentnodeid start with nodeid='$$'
最终版本: select treestr.id,treestr.path from (select nodeid,parentnodeid,id,name,sys_connect_by_path(name,'#')as path from ( select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from (select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid from a,b where a.id = b.id2 ) union ( select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual ) ) idtree connect by prior nodeid = parentnodeid start with nodeid='$$' )treestr , ( select nodeid from (select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from (select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid from a,b where a.id = b.id2 ) union ( select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual ))idtree2 where not exists (select parentnodeid from (select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from (select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid from a,b where a.id = b.id2 ) union ( select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual ))idtree3 where idtree2.nodeid = idtree3.parentnodeid) )leaves where treestr.nodeid = leaves.nodeid 呵呵,我是不是很变态?
如
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 name from id2=p_id;
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.name;
else
str:=str||'#'||v_sor.name;
end if;
num:=num+1;
end loop;
return str;
end;
/
select id,get_str(id) name from a;
from
(
select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from
(select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid
from a,b
where a.id = b.id2
)
union
(
select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual
)
) idtree
connect by prior nodeid = parentnodeid
start with nodeid='$$'
select treestr.id,treestr.path
from
(select nodeid,parentnodeid,id,name,sys_connect_by_path(name,'#')as path
from
(
select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from
(select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid
from a,b
where a.id = b.id2
)
union
(
select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual
)
) idtree
connect by prior nodeid = parentnodeid
start with nodeid='$$'
)treestr
,
(
select nodeid from
(select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from
(select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid
from a,b
where a.id = b.id2
)
union
(
select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual
))idtree2
where not exists (select parentnodeid from (select '$'||to_char(id)||to_char(nameid) as nodeid,case when nameid = 1 then '$$' else '$'||to_char(id)||to_char(nameid - 1) end parentnodeid,id,name from
(select a.id id,b.name name,rank() over(partition by a.id order by b.name) nameid
from a,b
where a.id = b.id2
)
union
(
select '$$' as nodeid,null as parentnodeid,null as id,null as name from dual
))idtree3 where idtree2.nodeid = idtree3.parentnodeid)
)leaves
where treestr.nodeid = leaves.nodeid
呵呵,我是不是很变态?