How can I limit columns returned based on their value?

Question

I have a table which looks like

index customer_number ABC CWD ROE BEE 
1                   1   0   0   0   1

and I want to return only the field names that have value 1 in this case 'BEE'

I found that by SHOW FIELDS I can get the names of the fields but how I can say show the field names where field value = 1?

Answer 1

You can't do this in a general way.

What you can do is write a sql statement like this:

select index, customer_number, decode (ABC, 1, "ABC", null) || decode (CWD, 1, "CWD", null) || decode (ROE, 1, "ROE", null) || decode (BEE, 1, "BEE", null) from aTable

It will display the column names for each entry where the value equals to one. It is oracle sql, so if you use a different rdbms the syntax will vary.

Answer 2

The beat it to death answer is to use CASE statements, one for each column. Something like:

SELECT CASE WHEN index=1 THEN "index" ELSE "no_index" END as i,
       CASE WHEN customer=1 THEN "customer" ELSE "no_customer" END as c,
       CASE WHEN ...

Answer 3

I would use CASE statement here.

SELECT 
  index, customer_number, 
  CASE
    WHEN abc=0 THEN 'abc'
    WHEN cwd=0 THEN 'cwd'
  END
FROM
  table_name

Answer 4

This is not something that SQL was really meant to do, and would be better done with application logic.

That said, if you really wanted to do it, you would probably need to involve a temp table and a SPROC:

1. Get the row and determine which fields are set.
2. Use that information to create a temp table with only the set fields.
3. Insert the data into that temp table, then select the rows from there.

It would be a huge mess of SQL to replace what would amount to only a few lines of application code. Probably not worth it.