CODEWITHSUNDEEP
mysql
user3906056
user3906056

Reputation: 313

How to show column name if it contains a specific value?

I'm trying to display the column name of the my table if it has the value 1

| A | B | C | D |
| 0 | 1 | 1 | 0 |

In this case i would like to get the result:

| Column |
| B      |
| C      |

I wrote the following query but it is not working:

 SHOW COLUMNS 
 FROM `questions` 
 WHERE VALUES=`1`

Upvotes: 0

Views: 1598

Answers (1)

Taryn
Taryn

Reputation: 247850

If you just need a list of the columns that contain the value = 1, you should be able to use the following query:

select col
from 
(
  select col, 
    case s.col
      when 'A' then A
      when 'B' then B
      when 'C' then C
      when 'D' then D
    end AS val
  from yourtable
  cross join
  (
    select 'A' AS col union all
    select 'B' union all
    select 'C' union all 
    select 'D'
  ) s
) s
where val = 1;

See SQL Fiddle with Demo. This uses a virtual table with your column names (A, B, etc) to unpivot your columns and then you just return only the column names that contain a value of 1. Credit for this technique goes to @Andriy M.

Upvotes: 2

Related Questions