create or replace function get(p_field1 in varchar2)
return varchar2 is
Result varchar2(100);
cursor rad_cursor(v_field1 varchar2) is
select distinct field2 from table_name
where id=v_field1 ;
v_count Number;
begin
v_count:=0; for v_sor in rad_cursor(p_field1) loop
if v_count=0 then
Result:=v_sor.field2;
v_count:=v_count+1;
else
Result:=Result||','||v_sor.field2;
end if;
end loop;
return(Result);
end get;
/select field1,get(field1) from table_name group by field1;
return varchar2 is
Result varchar2(100);
cursor rad_cursor(v_field1 varchar2) is
select distinct field2 from table_name
where id=v_field1 ;
v_count Number;
begin
v_count:=0; for v_sor in rad_cursor(p_field1) loop
if v_count=0 then
Result:=v_sor.field2;
v_count:=v_count+1;
else
Result:=Result||','||v_sor.field2;
end if;
end loop;
return(Result);
end get;
/select field1,get(field1) from table_name group by field1;
A表field2为 主键
B表field,field2为 外键
select distinct(field2),field1 from B;
??
distinct是对整行sql排重的,这样是不行的!
数据存放问题
要是
field1 field2
值 p1 a1,a2,a3
p2 a5,a7
这样,一个SQL是搞不定的
要在过程中用游标扫描每行记录,如出现符合条件的就往field2中并一条记录
请注意是并,而不是插。也就是每次都要把原来存在的记录并一下然后UPDATE原来的字段值
RETURNS Varchar(3000)
AS
BEGIN
declare @getstr Varchar(3000)
set @getstr=''
select @getstr=@getstr+[text] from tscoladd where id=@id
return(@getstr)
ENDselect field1, dbo.getstr(field1)
from tscoladd
group by field1
RETURNS Varchar(3000)
AS
BEGIN
declare @getstr Varchar(3000)
set @getstr=''
select @getstr=@getstr+field2 from tscoladd where id=@id
return(@getstr)
ENDselect field1, dbo.getstr(field1)
from tscoladd
group by field1
SQL> create table temp
2 ( A varchar2(10) ,
3 b varchar2(10));Table created.SQL>
SQL> insert into temp values ('p1','a1');1 row created.SQL> insert into temp values ('p1','a2');1 row created.SQL> insert into temp values ('p1','a3');1 row created.SQL> insert into temp values ('p2','a5');1 row created.SQL> insert into temp values ('p2','a7');1 row created.SQL> commit;Commit complete.SQL> select a , max(decode(c ,1 , b, '')) || max(decode(c ,2 , ',' || b, '')) || max(decode(c ,3 ,
',' || b , '')) NEW
2 from ( select a , b , row_number() over ( partition by a order by b) C from temp )
3 where c <= 3
4 group by a ;A NEW
---------- --------------------------------
p1 a1,a2,a3
p2 a5,a7