SQL query to get column names if it has specific value

Question

I have a situation here, I have a table with a flag assigned to the column names(like 'Y' or 'N'). I have to select the column names of a row, if it have a specific value.

My Table:

Name|sub-1|sub-2|sub-3|sub-4|sub-5|sub-6|
-----------------------------------------
Tom | Y   |     | Y   | Y   |     | Y   |
Jim | Y   |  Y  |     |     | Y   | Y   |
Ram |     |  Y  |     | Y   | Y   |     |

So I need to get, what are all the subs are have 'Y' flag for a particular Name. For Example:

If I select

Tom

I need to get the list of 'Y' column name in query output.

Subs
____
sub-1
sub-3
sub-4
sub-6

Your help is much appreciated.

Answer 1

The problem is that your database model is not normalized. If it was properly normalized the query would be easy. So the workaround is to normalize the model "on-the-fly" to be able to make the query:

select col_name
from (
   select name, sub_1 as val, 'sub_1' as col_name
   from the_table
   union all
   select name, sub_2, 'sub_2'
   from the_table
   union all
   select name, sub_3, 'sub_3'
   from the_table
   union all
   select name, sub_4, 'sub_4'
   from the_table
   union all
   select name, sub_5, 'sub_5'
   from the_table
   union all
   select name, sub_6, 'sub_6'
   from the_table
) t
where name = 'Tom'
and val = 'Y'

The above is standard SQL and should work on any (relational) DBMS.

Answer 2

Below code works for me.

select t.Subs from (select name, u.subs,u.val
from TableName s
unpivot
(
  val
  for subs  in (sub-1, sub-2, sub-3,sub-4,sub-5,sub-6,sub-7)
) u where u.val='Y') T
where t.name='Tom'