CODEWITHSUNDEEP
oracle-database
kavya
kavya

Reputation: 157

How to join tables in oracle where columns in one table are row data in another table

I am trying to write a query to join columns of one table to rows of another table in oracle. I am unable to understand how to do that. Please find the below details.

create table t1(
id number,
var1 number,
var2 number,
var3 number)

insert into t1(id,var1,var2,var3) values(1,11,12,13)
insert into t1(id,var1,var2,var3) values(2,21,22,23)
insert into t1(id,var1,var2,var3) values(3,31,32,33)


create table t2(
id number,
calculationType varchar2(500),
calculationOn varchar2(500),
newColName varchar2(500))

insert into t1(id,calculationType,calculationOn,newColName) values(1,'SUM', 'var1', 'SumOfVar1')
insert into t1(id,calculationType,calculationOn,newColName) values(2,'MAX', 'var2', 'MaxOfVar2')
insert into t1(id,calculationType,calculationOn,newColName) values(3,'AVG', 'var3', 'AvgOfVar3')

above are the tables and i want to apply the operation of calculationType(table 2 column) on calculationOn.

expected result is

    SumOfVar1, MaxOfVar2, AvgOfVar3
    63, 32, 23

where 63 is sum of var1 column, 32 is maximum of var2 column and 23 is avg of var3 column.

can anyone help me in getting the expected result. Thank you in advance.

I tried the below query but its not returning the expected result.

select (select concat(concat(concat(calculationType, '('),calculationOn), ')') from t2 where id = 1) from t1

and the result its giving is

var1
var1
var1

I am not able to understand why its not doing sum. can anyone explain me please

Upvotes: 1

Views: 273

Answers (1)

Littlefoot
Littlefoot

Reputation: 143033

As you don't know what expects you in t2, I'd suggest a function that dynamically composes a select statement (l_str in this example) and uses it as a source for refcursor.

SQL> create or replace function f_test
  2    return sys_refcursor
  3  is
  4    l_rc  sys_refcursor;
  5    l_str varchar2(200);
  6  begin
  7    for cur_r in (select id, calculationtype, calculationon, newcolname
  8                  from t2
  9                  order by id
 10                 )
 11    loop
 12      l_str := l_str ||
 13               cur_r.calculationtype ||'('    ||
 14               cur_r.calculationon   ||') as '||
 15               cur_r.newcolname      ||',' ;
 16    end loop;
 17    l_str := 'select ' || rtrim(l_str, ',') || ' from t1';
 18    open l_rc for l_str;
 19    return l_rc;
 20  end;
 21  /

Function created.

Testing:

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

 SUMOFVAR1  MAXOFVAR2  AVGOFVAR3
---------- ---------- ----------
        63         32         23


SQL>

Upvotes: 1

Related Questions