SQL query to retrieve column name among 3 columns in table whose values are mutually excluse ie.one-CodePudding

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 !!

CodePudding user response：

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

CodePudding user response：

Certainly Serg's CASE is the way to go 1

Just for fun, here is an option that will dynamically unpivot your data

Example or dbFiddle

Select A.ID
      ,Value = B.[Key]
From   YourTable A
Cross Apply ( Select * 
               From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper ))  
               Where [Key] not in ('id','OtherCol')
            ) B

Results

ID   Value
001  valid
002  invalid
003  non-scope