Gossamer Forum
Home : General : Databases and SQL :

help me plz...(oracle pl/sql problem)...

Quote Reply
help me plz...(oracle pl/sql problem)...
i use oracle as my database and
i have a table like this
ID TAG MIN AVG MAX
---------------------------------------
1101 Brite 12 15 16
1101 Dirt 2 4 7

how to make a view from table above,with the structure like this

ID BriteMin BriteAvg BriteMax DirtMin DirtAvg DirtMax
-------------------------------------------------------------------------
1101 12 15 16 2 4 7

plz reply me a.s.a.p plz....
Quote Reply
Re: [mayer_s] help me plz...(oracle pl/sql problem)... In reply to
This should do it, but don't expect greate performance if the table is large. You might want to look at using a materialized view if the table isn't updated too frequently.
Code:
select ID,
sum(BriteMin),
sum(BriteMax),
sum(BriteAvgx),
sum(DirtMin),
sum(DirtMax),
sum(DirtAvg)
from ( select ID,
decode(tag,'Brite', min,0) as BriteMin,
decode(tag,'Brite', max,0) as BriteMax,
decode(tag,'Brite', avg,0) as BriteAvg,
decode(tag,'Dirt', min,0) as DirtMin,
decode(tag,'Dirt', max,0) as DirtMax,
decode(tag,'Dirt', avg,0) as DirtAvg
from tablename)
group by ID;

Last edited by:

paulj: Jun 30, 2004, 5:29 PM