Convert column name as Column Value-CodePudding

I have a problem. There are 2 tables (Config and Data).
I want to select value from config table and use it as column name in data table.

i.e) By selecting id=1 from x_config, I wanted to get A,B,C

I can use a cursor to fetch the column name from x_config and can pass that value to another query inside my procedure, but I wanted to know if there is any other way in which I can achieve this in a single query.

create table ABC
(
name varchar2(20),
age number,
phone number
);

Insert into ABC Values('A',10,1111);
Insert into ABC Values('B',20,1122);
Insert into ABC Values('C',30,3333);

create table x_config
(
col varchar2(20),
id number
);

Insert into x_config Values('name',1);
Insert into x_config Values('age',2);
Insert into x_config Values('phone',3);


select col from x_config where id=1;

Thanks in Advance!

CodePudding user response：

You can use a CASE expression:

SELECT CASE (SELECT col FROM x_config WHERE id = 1)
       WHEN 'name'
       THEN name
       WHEN 'age'
       THEN TO_CHAR(age)
       WHEN 'phone'
       THEN TO_CHAR(phone)
       END AS value
FROM   abc

Which, for your sample data, outputs:

VALUE

A

B

C

db<>fiddle here

CodePudding user response：

Another approach is to use dynamic SQL; one way is to utilise an XML trick:

select xml.result
from x_config xc
cross apply xmltable('/ROWSET/ROW'
  passing dbms_xmlgen.getxmltype(
      'select ' || xc.col || ' as result from abc'
    )
  columns result
) xml
where xc.id = 1;

RESULT
A
B
C

db<>fiddle

The dbms_xmlgen call generates a dynamic statement using the column name from your lookup-up table and gets the result as an XML document. That is then used as the source for the XMLTable, which extracts the value.

You could get multiple values at once but you would need to know how many, so the columns clause matches. And you can't get the original column name(s) for the result set; you'd need a different form of dynamic SQL and a ref cursor for that, I think.