CODEWITHSUNDEEP
sqloracle-databasejsp
Eduardo
Eduardo

Reputation: 45

Get column names of row if value is 1

I have a table like:

+--------+--------+--------+--------+--------+
| Class  | Skill1 | Skill2 | Skill3 | Skill4 |
+--------+--------+--------+--------+--------+
| Strong |      1 |      0 |      0 |      0 |
| Tough  |      0 |      1 |      1 |      0 |
| Smart  |      0 |      0 |      0 |      1 |
+--------+--------+--------+--------+--------+

Im trying to dynamically create a select dropdown on another page (This is not the problem). I want an Oracle SQL statement that can select the column names of all the columns that have a 1 in a specified row.

Example: I choose Strong class and would like to know which skills I can use. So I need an SQL statement that will give me only the column names with a 1 in the field for just the Strong row. For this example, it should return (Skill1).

Upvotes: 2

Views: 57

Answers (2)

sgeddes
sgeddes

Reputation: 62851

I'd recommend changing your database structure -- what if you need to add another skill, you're going to have to keep adding new columns. Consider creating a skills table and then relating those to classes via another table. At that point, it's a simple select statement.

However, if that isn't an option in your case, one option using your current design would be to use union all:

select skill
from (
    select class, case when skill1 = 1 then 'Skill1' end Skill
    from yourtable
    union all 
    select class, case when skill2 = 1 then 'Skill2' end Skill
    from yourtable
    union all 
    select class, case when skill3 = 1 then 'Skill3' end Skill
    from yourtable
) t
where class = 'Strong' and skill is not null

Upvotes: 3

Aramillo
Aramillo

Reputation: 3226

You can use unpivot and then do a simple query where class = Strong and the skill = 1:

with t( Class  , Skill1 , Skill2 , Skill3 , Skill4 ) as 
(
select  'Strong' ,      1 ,      0 ,      0 ,      0 from dual union all
select  'Tough'  ,      0 ,      1 ,      1 ,      0 from dual union all 
select  'Smart'  ,      0 ,      0 ,      0 ,      1  from dual ) -- Sample data
select skills from (
select * from t
unpivot( 
skill for skills in (Skill1 , Skill2 , Skill3 , Skill4)
) ) where class = 'Strong' and skill = 1 ;

Upvotes: 1

Related Questions