CODEWITHSUNDEEP
sqloracle-database
Jason Yang
Jason Yang

Reputation: 157

How to write a sql to dynamically add some calculated rows in Oracle?

I have a table like this:

id  name    value   
1   elec    10  
1   water   20  
2   elec    15  
2   water   45

Now I need to dynamically add some rows to the result of select query:

id  name    value   
1   elec    10  
1   water   20  
1   ratio   0.5

2   elec    15  
2   water   45  
2   ratio   0.33

Add two rows dynamically,how can i do?

Upvotes: 0

Views: 556

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

If you are fine with slight change in ordering, try this.

SELECT id,name,value FROM yourtable 
UNION ALL
SELECT 
                a.id , 
                'ratio' name,  
                a.value/b.value value 
        FROM
                yourtable a 
        JOIN    yourtable b on a.id = b.id
        WHERE   a.name  = 'elec' 
                and b.name  = 'water'
ORDER BY 
        id ,  
        VALUE DESC;

If you need to add the rows to table itself, then use.

INSERT INTO yourtable 
SELECT
        a.id  ,
        'ratio'  name,
        a.value/b.value value
FROM
        yourtable a
JOIN    yourtable b on a.id = b.id
WHERE   a.name     ='elec'
        and b.name ='water';

Upvotes: 0

user5683823
user5683823

Reputation:

It would make a lot more sense to "present" the results with ELEC, WATER and RATIO columns - one row per ID. The solution below shows how you can do that efficiently (reading the base table only one time).

with
  inputs ( id, name, value ) as (
    select 1, 'elec' , 10 from dual union all 
    select 1, 'water', 20 from dual union all
    select 2, 'elec' , 15 from dual union all
    select 2, 'water', 45 from dual
  )
-- End of simulated inputs (not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select id, elec, water, round(elec/water, 2) as ratio
from   inputs
pivot  ( min(value) for name in ('elec' as elec, 'water' as water ) )
;

        ID       ELEC      WATER      RATIO
---------- ---------- ---------- ----------
         1         10         20         .5
         2         15         45        .33

If instead you need the results in the format you showed in your original post, you can unpivot like so (still reading the base table only once):

with
  inputs ( id, name, value ) as (
    select 1, 'elec' , 10 from dual union all 
    select 1, 'water', 20 from dual union all
    select 2, 'elec' , 15 from dual union all
    select 2, 'water', 45 from dual
  )
-- End of simulated inputs (not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select id, name, value
from (
    select id, elec, water, round(elec/water, 2) as ratio
    from   inputs
    pivot  ( min(value) for name in ('elec' as elec, 'water' as water ) )
  )
unpivot ( value for name in (elec as 'elec', water as 'water', ratio as 'ratio') )
;

        ID NAME       VALUE
---------- ----- ----------
         1 elec          10
         1 water         20
         1 ratio         .5
         2 elec          15
         2 water         45
         2 ratio        .33

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Here is one method:

with t as (
      <your query here>
     )
select id, name, value
from ((select t.*, 1 as ord
       from t
      ) union all
      (select id, 'ratio',
              max(case when name = 'elec' then value end) / max(case when name = 'water' then value end)
              ), 2 as ord
      from t
      group by id
     )
    ) tt
order by id, ord;

Upvotes: 0

Related Questions