Select column names that match a criteria (MySQL)

Question

I have the following structure in my DB:

id,col_a,col_b,col_c,etc...

Now, all the other columns except for id are of type boolean. Lets say for example that

col_a=1,
col_b=0,
col_c=1

I am looking for a way to return the names of the columns where the column is true (=1), so in this example the return should look something like col_a,col_c

There will be a dynamic number of columns, seeing as the table is altered often to add new columns and delete old ones.

The function I have thus far looks like this - it is the function that is supposed to return that string of column names...

DROP FUNCTION fn_access;

DELIMITER //;

CREATE FUNCTION fn_access (myid INT) RETURNS varchar(800)
   DETERMINISTIC
BEGIN
            DECLARE ret_val VARCHAR(800);
            DECLARE col_name VARCHAR(255);
            DECLARE i INT;
            DECLARE num_rows INT;

            DECLARE col_names CURSOR FOR

            SELECT column_name
            FROM information_schema.columns
            WHERE `table_name` = 'access' AND `table_schema` = 'some_db' AND `column_name` <> 'id'
            ORDER BY ordinal_position;

            SELECT FOUND_ROWS() into num_rows;

            SET i = 1;
            the_loop: LOOP

            IF i > num_rows THEN
                            CLOSE col_names;
                            LEAVE the_loop;
            END IF;


            FETCH col_names 
            INTO col_name;

            SET ret_val = CONCAT(',' col_name);

            SET i = i + 1;  
            END LOOP the_loop;      

            SELECT * FROM access WHERE id = @myid;

            RETURN ret_val;
END
//

Is there any way to do this using straight SQL? I am using MySQL.

columns where the column is true ??? You mean column itself or corresponding row values — Ravindra Shekhawat, Apr 17 '13 at 06:59
yes, the row value. So in other words, I want `col_a` if `col_a = 1`, `col_b` if `col_b = 1`, etc. — iLikeBreakfast, Apr 17 '13 at 07:02

fthiella · Accepted Answer · 2013-04-17T07:18:38.827

3

If I understand your question correctly, maybe you need something like this:

SELECT 'col_a' col
FROM yourtable
WHERE col_a
UNION
SELECT 'col_b'
FROM yourtable
WHERE col_b
UNION
SELECT 'col_c'
FROM yourtable
WHERE col_c
...

this will return all columns in your table that have at least one row where they are true.

Or maybe this:

SELECT
  id,
  CONCAT_WS(', ',
    CASE WHEN col_a THEN 'col_a' END,
    CASE WHEN col_b THEN 'col_b' END,
    CASE WHEN col_c THEN 'col_c' END) cols
FROM
  yourtable

that will return rows in this format:

| ID | COLS                |
----------------------------
|  1 | col_a, col_c        |
|  2 | col_a, col_b, col_c |
|  3 |                     |
|  4 | col_c               |
...

Please see fiddle here. And if you need to do it dynamically, you could use this prepared statement:

SELECT
  CONCAT(
    'SELECT id, CONCAT_WS(\', \',',
  GROUP_CONCAT(
    CONCAT('CASE WHEN ',
           `COLUMN_NAME`,
           ' THEN \'',
           `COLUMN_NAME`,
           '\' END')),
    ') cols FROM yourtable'
  )
FROM
  `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE
  `TABLE_NAME`='yourtable'
  AND COLUMN_NAME!='id'
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Fiddle here.

edited Apr 17 '13 at 07:18

answered Apr 17 '13 at 07:05

fthiella

48,073
15
90
106

Thanks for the answer, I will implement it for now. But that doesn't cater for, say, 200 different col's? This could work, but this doesn't solve the issue of the table columns changing constantly. Is there a way I could loop through all the columns in the table and execute this on all of them? – iLikeBreakfast Apr 17 '13 at 07:18
@user1795229 i've updated my answer, i added a way to create the query dynamically, i hope it's okay – fthiella Apr 17 '13 at 07:20
That's brilliant! That will work very well. Just one more question, how will I return that concatenated string into the function I wrote above? The whole idea is to get the concatenated string out using the function above for later use. – iLikeBreakfast Apr 17 '13 at 07:26
@user1795229 tnx, instead of using a function, I would prepare a parametrized statement, and then execute it with a different id, see it here http://sqlfiddle.com/#!2/8f4ee/17 but please let me know if you need a function – fthiella Apr 17 '13 at 07:32
what you have there is brilliance, but it has to work with a function unfortunately. This is because that function will be used in other SQL statements and for third party software. We have to be able to return that string as part of the result set of another query. – iLikeBreakfast Apr 17 '13 at 07:38
I've tried to implement this into the function above, but got the following error: `#1336 - Dynamic SQL is not allowed in stored function or trigger`. Any ideas? – iLikeBreakfast Apr 17 '13 at 08:17

Select column names that match a criteria (MySQL)

1 Answers1

Linked