[askdba] Re: SQL Doubt

  • From: "Subodh Deshpande" <subodh_deshpande@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Wed, 20 Oct 2004 23:14:07 -0500

hi Madhu,

how are you !
this is matrix/cross tab report..
decode the second column to a returanable multirow function value..

eg.
SELECT a.plant_cd, a.wo_catg, min(dp_sch_wdt) dp_sch_wdt,
nvl(count(decode(to_char(a.dp_sch_wdt,'DD'),'07',1)),0) I_C,
nvl(count(decode(to_char(a.dp_sch_wdt,'DD'),'14',1)),0) II_C,
nvl(count(decode(to_char(a.dp_sch_wdt,'DD'),'21',1)),0) III_C,
nvl(count(decode(to_char(a.dp_sch_wdt,'DD'),'30',1)),0) IV_C,
nvl(sum(decode(to_char(a.dp_sch_wdt,'DD'),'07',dp_qty_ton)),0) I_T,
nvl(sum(decode(to_char(a.dp_sch_wdt,'DD'),'14',dp_qty_ton)),0) II_T,
nvl(sum(decode(to_char(a.dp_sch_wdt,'DD'),'21',dp_qty_ton)),0) III_T,
nvl(sum(decode(to_char(a.dp_sch_wdt,'DD'),'30',dp_qty_ton)),0) IV_T
FROM desp_plan_tran a
where plant_cd =3D nvl(:p_plant_cd,plant_cd) and
           dp_year =3D :p_dp_year and
           dp_month =3D :p_dp_month
group by a.plant_cd, a.wo_catg
order by a.plant_cd, a.wo_catg

take care..subodh

----- Original Message -----
From: "Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
To: <askdba@xxxxxxxxxxxxx>
Subject: [askdba] SQL Doubt
Date: Tue, 19 Oct 2004 15:08:12 +0900

>=20
> Dear Friends,
>       We have two tables loan,entity_loan.=3D20
>=20
>       Loan -> Loan_id
>       Entity_loan -> Loan_id,Entity_id,Entity_type
>=20
> Values in the Loan table
> Loan_ID
> L1
> L2
> L3
>=20
> Values in Entity_loan Table
> Loan_ID               Entity_ID       Entity_Type
> L1            E1              B
> L1            E1              G
> L1            E2              B
> L1            E3              G
>=20
>=20
> I need the output like=3D20
>=20
> Loan_ID               Borrower1       Borrwer2        Guar1           Guar2
> L1            E1              E2              E1              E3
>=20
> Please if any one can have any idea let me know.
>=20
> Thanks in Advance
> Mudhalvan M.M
>=20
>=20



Good Luck..Subodh Deshpande

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Experience Is Knowledge
Wisdom Is Philosophy
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D







--=20
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as=
p?SRC=3Dlycos10


Other related posts: