Create Table henry_test (a varchar2(10),b int); Insert Into henry_test values ('aa',1); Insert Into henry_test values ('bb',1); Insert Into henry_test values ('cc',1); Insert Into henry_test values ('dd',2); Insert Into henry_test values ('ee',2); Insert Into henry_test values ('ff',3); Insert Into henry_test values ('gg',3); Insert Into henry_test values ('hh',3); Commit; /* SQL> select * from henry_test;A B ---------- --------------------------------------- aa 1 bb 1 cc 1 dd 2 ee 2 ff 3 gg 3 hh 38 rows selected */create or replace function f_henry_ConcatRowsByColumn( Column2Value in Varchar2, --分组该列的值 ColumnName1 in Varchar2, --要连接的列名 ColumnName2 in Varchar2, --用来做分组依据的列名 TableName in Varchar2 --表名 ) return varchar2 is v_Result varchar2(32767); type cur_type is ref cursor; myCur cur_type; v_Column1Value varchar2(4000); begin Open myCur for 'Select '||ColumnName1||' From '||TableName||' Where '||ColumnName2||' = '||Column2Value; Loop Fetch myCur Into v_Column1Value; Exit When myCur%notfound; v_Result:=v_Result||v_Column1Value||','; End Loop; Close myCur; return(v_Result); end f_henry_ConcatRowsByColumn; /* SQL> select B,f_henry_ConcatRowsByColumn(B,'A','B','henry_test') from henry_test group by B; B F_HENRY_CONCATROWSBYCOLUMN(B,' --------------------------------------- -------------------------------------------------- 1 aa,bb,cc, 2 dd,ee, 3 ff,gg,hh, */ 上面的操作仅限于一张表内。
楼主可以这样用 select objid,f_henry_ConcatRowsByColumn(objid,'objName','objid','henry_subtest') from henry_test group by objid
Insert Into henry_test values ('aa',1);
Insert Into henry_test values ('bb',1);
Insert Into henry_test values ('cc',1);
Insert Into henry_test values ('dd',2);
Insert Into henry_test values ('ee',2);
Insert Into henry_test values ('ff',3);
Insert Into henry_test values ('gg',3);
Insert Into henry_test values ('hh',3);
Commit;
/*
SQL> select * from henry_test;A B
---------- ---------------------------------------
aa 1
bb 1
cc 1
dd 2
ee 2
ff 3
gg 3
hh 38 rows selected
*/create or replace function f_henry_ConcatRowsByColumn(
Column2Value in Varchar2, --分组该列的值
ColumnName1 in Varchar2, --要连接的列名
ColumnName2 in Varchar2, --用来做分组依据的列名
TableName in Varchar2 --表名
)
return varchar2 is
v_Result varchar2(32767);
type cur_type is ref cursor;
myCur cur_type;
v_Column1Value varchar2(4000);
begin
Open myCur for 'Select '||ColumnName1||' From '||TableName||' Where '||ColumnName2||' = '||Column2Value;
Loop
Fetch myCur Into v_Column1Value;
Exit When myCur%notfound;
v_Result:=v_Result||v_Column1Value||',';
End Loop;
Close myCur;
return(v_Result);
end f_henry_ConcatRowsByColumn;
/*
SQL> select B,f_henry_ConcatRowsByColumn(B,'A','B','henry_test') from henry_test group by B; B F_HENRY_CONCATROWSBYCOLUMN(B,'
--------------------------------------- --------------------------------------------------
1 aa,bb,cc,
2 dd,ee,
3 ff,gg,hh,
*/
上面的操作仅限于一张表内。
select objid,f_henry_ConcatRowsByColumn(objid,'objName','objid','henry_subtest')
from henry_test group by objid