Denormalize SQL Query ???

Hello,

I want result of following query in one row. This Formno 13534 has 3 risks, which I want to denorlmalize into one record.

So result will show like this...

formno risk1 risk2 risk3, desp
13534 1 2 3 SSR

I tried following way. which gives 3 records. Table has Formno,risk,desp fields. Please correct me in this.

select formno,decode(rownum,1,risk) risk1,decode(rownum,2,risk) risk2,decode(rownum,3,risk) risk3,desp from Formrisks where Desp='SSR'and Fromno=13534 and rownum<=3;


Thanks & Regards
Shailesh

Take a look at this thread 'can we generate a matrix report in oracle?' - an ongoing discussion on this very topic.

HTH

David.

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;

Hi !!

I feel this will work,

<< SELECT col1||' '||col2||' ' FROM table_name; >>

If it works in your case then cheers !!!

Shan

Quote:

---

Originally posted by bensr
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;

---

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;