[QUOTE][i]Originally posted by bensr [/i]
[B]FIRST OPTION IS THE NEXT:
select f1.formno
, f1.risk risk1
, f2.risk risk2
, f3.risk risk3
, f1.desp
from Formrisks f1
, Formrisks f2
, Formrisks f3
where f1.Desp = F2.Desp
and f1.Desp = f3.Desp
and f1.Desp ='SSR'
and f1.Fromno = f2.Fromno
and f1.Fromno = f3.Fromno
and f1.Fromno = 13534
and f1.risk < f2.risk
and f2.risk < f3.risk
;

better maybe to define a function
with a cursor to retrieve all the risk values
and put them in a return field

create or replace function get_risks(p_desp Formrisks.Desp%type
,p_fromno Formrisks.Fromno%type)
return varchar
is
cursor c_risk(p_desp Formrisks.Desp%type
,p_fromno Formrisks.Fromno%type)
is
select risk
from Formrisks
where Desp = p_desp
and Fromno = p_fromno
;
v_Result varchar2(60) ;
begin
for vc_risk in c_risk(p_desp, p_fromno)
loop
if v_Result is Null
then
v_Result := vc_risk.risk;
else
v_Result := v_Result||' - '||vc_risk.risk;
end if;
end loop;
RETURN v_Result;
end get_risks; [/B][/QUOTE]

Dear Friends

Thanks all for giving suggestions. I have done following way which gives correct result for any one formno. But when I remove formno in where condition I gives wrong output. Here there can be upto 100 risks for 1 formno, but I want only first 9 of each formno. Please correct my query incase anyone knows about it.

Thanks & Regards
Shailesh



select
max(decode(mod(rownum,100),1,risk)) risk1
,max(decode(mod(rownum,100),2,risk)) risk2
,max(decode(mod(rownum,100),3,risk)) risk3
,max(decode(mod(rownum,100),4,risk)) risk4
,max(decode(mod(rownum,100),5,risk)) risk5
,max(decode(mod(rownum,100),6,risk)) risk6
,max(decode(mod(rownum,100),7,risk)) risk7
,max(decode(mod(rownum,100),8,risk)) risk8
,max(decode(mod(rownum,100),9,risk)) risk9
,formno,desp
from Formrisks where desp='SSR' and formno=13533
group by trunc(mod(rownum,9)/9),formno,desp;