How do I get the column name of a Null table?
Consider the following table
--------------------------------
ID | ColA | ColB | ColC
--------------------------------
1 | ABC | |
2 | | XYZ |
3 | PQR | |
4 | MNO | PQR |
I need to get the first free table of a table where ID = 1. How can I do this
For example:
If ID = 1
, next free column ColB
If ID = 2
, next free column ColA
If ID = 3
, next free column ColB
If ID = 4
, next free columnColC
+3
user1556433
source
to share
4 answers
if you want the column name can be done like this:
SQL> select id, cola, colb, colc,
2 coalesce(nvl2(cola, '', 'COLA'),
nvl2(colb, '', 'COLB'),
nvl2(colc, '', 'COLC')) first_free_col
3 from tab;
ID COL COL COL FIRST_FREE_COL
---------- --- --- --- --------------------------------
1 ABC COLB
2 XYZ COLA
3 PQR COLB
4 MNO PQR COLC
or case
SQL> select id, cola, colb, colc,
2 case when cola is null then 'COLA'
3 when colb is null then 'COLB'
4 when colc is null then 'COLC'
5 end first_free_col
6 from tab;
ID COL COL COL FIRST_FREE_COL
---------- --- --- --- --------------------------------
1 ABC COLB
2 XYZ COLA
3 PQR COLB
4 MNO PQR COLC
+1
source to share