mysql - Get column name dynamically by Specific row value

Question

I am struck at writing a query. Here I want to show the column name based on some specific value

For Instance, my table is like this:

id  | fruits   |vegetables    |softdrink
-----------------------
1   | apple    | Onion        | Pepsi
2   | mango    | Potato       | Coke    
3   | banana   | Bringal      | RedBull

If I have a value "mango", then I should get the column name as fruit or

If I have a value "RedBull", then I should get the column name as softdrink

NOTE: I have many columns around 48 to get the name from any one of them

Answer 1

set @q= CONCAT('SELECT columns.column_name 
                from table inner 
                join information_schema.columns 
                on columns.table_schema = "dbname" 
                and columns.table_name = "table" 
                and ((',
                (SELECT GROUP_CONCAT(CONCAT('columns.column_name="',column_name,'"',' and table.',column_name,' = "value','"') SEPARATOR ' OR ')
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE table_name = 'table'),
                '))');
prepare query from @q;
execute query;

This works for sure..

Phew!

Fiddle: http://sqlfiddle.com/#!2/9420c/2/2

PS: Replace table with your table name ,dbname with your db name and value with your value