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

Question

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`

Are you looking for a select statement? `SELECT * FROM questions WHERE A = 1`? — knittl, Sep 11 '14 at 15:54
that's not how SQL works. `show columns` gives you details about the STRUCTURE of a table, and has NOTHING to do with the data in the table. — Marc B, Sep 11 '14 at 15:55
I don't think what you are trying is doable using SQL queries alone. — Parris Varney, Sep 11 '14 at 15:58
This question is actually bad because it tries to do something that SQL was not designed to address revealing a really inappropriate schema design. Even though the wanted output can be given with complex dynamic SQL, it's only for the given output. The OP has clearly not even thought what should happen if the table has 2 (or more) rows where a column has 1 in some rows and 0 in others. Which enhances even more the issues in the design (@user3906056, tables do have more than one row occasionally) — ypercubeᵀᴹ, Sep 11 '14 at 16:09
@Brewal That is correct Brewal. I would like to receive the table name if the values is 1. I thought this was doable with a sql question? — user3906056, Sep 11 '14 at 16:19
@bluefeet yes, i want a list of columns if it has the value 1 — user3906056, Sep 11 '14 at 16:44
@user3906056 I've edited your question with what I think you want, please review and let me know if that is correct? — Taryn, Sep 11 '14 at 17:03

score 2 · Accepted Answer · edited May 23 '17 at 12:00

2

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.

edited May 23 '17 at 12:00

Community

1
1

answered Sep 11 '14 at 17:05

Taryn

242,637
56
362
405

thx for taking your time, i relay appreciate it! Is this something that is extra hard for the mysql server to manage? And why cant i use where id=2 in my http://sqlfiddle.com/#!2/415845/2 sql fiddle – user3906056 Sep 11 '14 at 19:25
@user3906056 You have to include the ID column in the subquery -- http://sqlfiddle.com/#!2/415845/3 – Taryn Sep 11 '14 at 19:27

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

1 Answers1