CODEWITHSUNDEEP
sqloracle-databaseoracle11g
hbit
hbit

Reputation: 969

Select column based on value of another column's value

I have the following table in an Oracle database:

ID    COLUMN_NAME    FIELD1    FIELD2    FIELD3
-------------------------------------------------
1     FIELD1         red       blue      yellow
2     FIELD3         black     purple    green
3     FIELD2         grey      brown     white

I want to return the value of one of the FIELD columns based on the value of the COLUMN_NAME.

Desired result:

ID    COLUMN_NAME    FIELD_VALUE
---------------------------------
1     FIELD1         red
2     FIELD3         greeen
3     FIELD2         brown

Yes, I could do this with a CASE or IF / THEN / ELSE construct, however in reality I have about 50 of that FIELD columns that tend to become more over time. So that would become a beast of a query that would also have to be maintained.

I therefore am searching for a flexible solution like (pseudo-code)

SELECT ID, COLUMN_NAME, VALUE_OF_COLUMN(COLUMN_NAME) AS FIELD_VALUE FROM MYTABLE

Is there something like this in Oracle SQL without using temporary tables, without refactoring the table structure and without using scripting like PL/SQL?

Thanks!

Upvotes: 2

Views: 18723

Answers (3)

Praveen Pujar
Praveen Pujar

Reputation: 329

I had similar requirement and I used INSTR function from oracle

ex: INSTR(COLUMN1,COLUMN2) > 0 which returns position of the value of column2 in column1. We can use this condition in WHERE clause, so it returns all the values.

Upvotes: 0

MT0
MT0

Reputation: 167774

There is no "flexible" solution unless you are going to use dynamic SQL to get each value (and that will probably seriously degrade performance).

You can use CASE:

CASE column_name
     WHEN 'FIELD1' THEN field1
     WHEN 'FIELD2' THEN field2
     WHEN 'FIELD3' THEN field3
                   ELSE 'default'
     END AS field_value

or DECODE:

DECODE( column_name, 'FIELD1', field1, 'FIELD2', field2, 'FIELD3', field3, 'default' )
  AS field_value

or by un-pivoting the table. However all these solution will require updating if you change the number of columns.

The other way of doing it it to refactor your tables:

CREATE TABLE field_values (
  id          INT,
  field_name  VARCHAR2(20),
  field_value VARCHAR2(20),
  CONSTRAINT field_values__id_fn__pk PRIMARY KEY ( id, field_name )
);

CREATE TABLE ids (
  id          INT PRIMARY KEY,
  column_name VARCHAR2(20),
  CONSTRAINT ids__id_cn__fk FOREIGN KEY ( id, column_name )
                            REFERENCES field_names ( id, field_name )
);

INSERT INTO field_names
SELECT 1, 'FIELD1', 'red'    FROM DUAL UNION ALL
SELECT 1, 'FIELD2', 'blue'   FROM DUAL UNION ALL
SELECT 1, 'FIELD3', 'yellow' FROM DUAL UNION ALL
SELECT 2, 'FIELD1', 'black'  FROM DUAL UNION ALL
SELECT 2, 'FIELD2', 'purple' FROM DUAL UNION ALL
SELECT 2, 'FIELD3', 'green'  FROM DUAL UNION ALL
SELECT 3, 'FIELD1', 'grey'   FROM DUAL UNION ALL
SELECT 3, 'FIELD2', 'brown'  FROM DUAL UNION ALL
SELECT 3, 'FIELD3', 'white'  FROM DUAL;

INSERT INTO ids
SELECT 1, 'FIELD1' FROM DUAL UNION ALL
SELECT 2, 'FIELD3' FROM DUAL UNION ALL
SELECT 3, 'FIELD2' FROM DUAL;

SELECT i.id,
       i.column_name,
       f.field_value
FROM   ids i
       INNER JOIN field_values f
       ON ( i.id = f.id AND i.column_name = f.field_name );

Upvotes: 6

Avi
Avi

Reputation: 1145

As far as I knows there is no such inbuilt function in Oracle. You need to loop in once your entire table and then fetch expected record using dynamc sql.

Upvotes: 0

Related Questions