2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Plsql get column names by table value

I have a table T with one row value only, the names are column names and "not" or "enrolled" are values

Jack      |    Mary  |   Tom    |  Peter |

-------        ---------   -------        -------

enrolled  |   not    |enrolled  | not   |

Is there any way I can return the column names where their values are "not"

sample result set:

| Mary | Peter |

 

 

userimage

You seem to have a very bad data format. And a SQL query returns a fixed number of columns. So, you cannot do what you want by returning values.

If there is still time to redesign your table + data, consider changing it to something like this instead:

 name  |  is_enrolled

 ----     -----------

 Jack          1

 Mary          0

  Tom          1

Peter          0

This way, you can use simpler SQL to do what you want:

select name from table_name where is_enrolled = 0;

Or

select ((case when Jack = 'not' then 'Jack ' else '' end) ||

        (case when Mary = 'not' then 'Mary ' else '' end) ||

        (case when Tom = 'not' then 'Tom ' else '' end) ||

        (case when Peter = 'not' then 'Peter ' else '' end)

       ) TheNots

from t;

Answer is