How can I extract columns name from row value of another table(oracle sql)?

Question

I have 2 tables:

table1

no	a	b	c
x1	2	3	4
x2	10	11	12
x3	20	21	22

table2

from_val	in_out	cf_pv	term
a	out	cf	b
b	out	pv	b
c	in	cf	e

Define sum_out is sum of a, b, c in table1 with condition in_out='out' in table2 and sum_cf is sum of a, b, c in table1 with condition cf_pv='cf' in table2.

Shortly, values of from_val in table2 are columns name i.e. a, b, c in table1.

How can I extract and calculate sum_out or sum_cf of every no in Oracle?

sum_out of x1 = 2 + 3
sum_out of x2 = 10 + 11
sum_out of x3 = 20 + 21

sum_cf of x1 = 2 + 4
sum_cf of x2 = 10 + 12
sum_cf of x3 = 20 + 22

Thanks!

'''''''''''''''''''''''''''''''''''''''''''''

in additional,

i want to calculate

sum_out and cf of x1= 2 (=a)
sum_out and cf of x2= 10 (=b)
sum_out and cf of x3= 20 (=c)

d r · Accepted Answer · 2022-11-30 12:47:07Z

Sample data

WITH
    tbl_1 AS
        (
            Select 'x1' "COL_NO",  2 "A",  3 "B",  4 "C" From Dual Union All
            Select 'x2' "COL_NO", 10 "A", 11 "B", 12 "C" From Dual Union All
            Select 'x3' "COL_NO", 20 "A", 21 "B", 22 "C" From Dual 
        ),
    tbl_2 AS
        (
            Select 'A' "FROM_VAL",  'out' "IN_OUT",  'cf' "CF_PV",  'begin' "TERM" From Dual Union All
            Select 'B' "FROM_VAL",  'out' "IN_OUT",  'pv' "CF_PV",  'begin' "TERM" From Dual Union All
            Select 'C' "FROM_VAL",  'in'  "IN_OUT",  'cf' "CF_PV",  'end' "TERM" From Dual          
        ),

Create CTE (formulas) that generates formulas for IN_OUT = 'out' and For CF_PV = 'cf'

  formulas AS
    (
        Select
            CASE WHEN IN_OUT = 'out' THEN IN_OUT END "IN_OUT", 
            LISTAGG(FROM_VAL, ' + ') WITHIN GROUP (ORDER BY FROM_VAL) OVER(PARTITION BY IN_OUT) "IN_OUT_FORMULA", 
            CASE WHEN CF_PV = 'cf' THEN CF_PV END "CF_PV",  
            LISTAGG(FROM_VAL, ' + ') WITHIN GROUP (ORDER BY FROM_VAL) OVER(PARTITION BY CF_PV) "CF_PV_FORMULA"
        From
            tbl_2
    ),

IN_OUT	IN_OUT_FORMULA	CF_PV	CF_PV_FORMULA
	C	cf	A + C
out	A + B	cf	A + C
out	A + B		B

Another CTE (grid) to connect COL_NO to formulas

  grid AS
    (
        Select   
            t1.COL_NO,
            CASE WHEN f1.IN_OUT = 'out' THEN f1.IN_OUT END "IN_OUT", CASE WHEN f1.IN_OUT = 'out' THEN f1.IN_OUT_FORMULA END "IN_OUT_FORMULA", 
            CASE WHEN f1.CF_PV = 'cf' THEN f1.CF_PV END "CF_PV", CASE WHEN f1.CF_PV = 'cf' THEN f1.CF_PV_FORMULA END "CF_PV_FORMULA"
        From
            tbl_1 t1
        Left Join
            formulas f1 ON(f1.IN_OUT Is Not Null AND f1.CF_PV Is Not Null)
   )

COL_NO	IN_OUT	IN_OUT_FORMULA	CF_PV	CF_PV_FORMULA
x1	out	A + B	cf	A + C
x2	out	A + B	cf	A + C
x3	out	A + B	cf	A + C

Main SQL to get the final result

    SELECT
        g.COL_NO,
        g.IN_OUT,
        g.IN_OUT_FORMULA,
        CASE WHEN g.IN_OUT = 'out' And INSTR(IN_OUT_FORMULA, 'A') > 0 THEN A ELSE 0 END + 
        CASE WHEN g.IN_OUT = 'out' And INSTR(IN_OUT_FORMULA, 'B') > 0 THEN B ELSE 0 END + 
        CASE WHEN g.IN_OUT = 'out' And INSTR(IN_OUT_FORMULA, 'C') > 0 THEN C ELSE 0 END "CALC_OUT",
        --
        g.CF_PV,
        g.CF_PV_FORMULA,
        CASE WHEN g.CF_PV = 'cf' And INSTR(CF_PV_FORMULA, 'A') > 0 THEN A ELSE 0 END + 
        CASE WHEN g.CF_PV = 'cf' And INSTR(CF_PV_FORMULA, 'B') > 0 THEN B ELSE 0 END +  
        CASE WHEN g.CF_PV = 'cf' And INSTR(CF_PV_FORMULA, 'C') > 0 THEN C ELSE 0 END "CALC_CF"
    FROM
        grid g
    INNER JOIN
        tbl_1 t1 ON(g.COL_NO = t1.COL_NO)

R e s u l t :

COL_NO	IN_OUT	IN_OUT_FORMULA	CALC_OUT	CF_PV	CF_PV_FORMULA	CALC_CF
x1	out	A + B	5	cf	A + C	6
x2	out	A + B	21	cf	A + C	22
x3	out	A + B	41	cf	A + C	42

Collectives™ on Stack Overflow

How can I extract columns name from row value of another table(oracle sql)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related