SQL query to retrieve column name among 3 columns in table whose values are mutually excluse ie.one among them will be ‘Y’ whose col name to be found

I have a query in which multiple joins and select from various tables are used. One table structure is as follows: Only one column among 3 will have the value ‘Y’.

Table employee :

id	valid	invalid	non-scope
001	Y	null	null
002	null	Y	null
003	null	null	Y

The o/p of the select statement for the multiple joins and tables should have only one value from the above table and that will be the column name of this table. The value has to be as below, column name of corresponding ‘Y’ value :

id	value
001	valid
002	invalid
003	non-scope

Please suggest a solution !!

Answer

You can use case

select id, case 'Y' 
      when valid then 'valid'
      when invalid then 'invalid'
      when [non-scope] then 'non-scope'
   end value
from employee