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

How about: http://stackoverflow.com/questions/8676354/sql-selecting-columns-based-on-column-value-from-another-table — Luceos, May 18 '13 at 07:19
if both `e` and `g` in `record` then what should return??@zan — Deval Shah, May 18 '13 at 07:25
@DevalShah if e or g is in record then i should get col2 and col1 respectively — Afsar, May 18 '13 at 07:27
@zan `if i have a value "g", then i should get the name as col3 ` in which condition you shoul get `col3`.Please be more specific and `Edit`your question with `Expected Output` — Deval Shah, May 18 '13 at 07:32
@DevalShah i asked if i have value as g then i should get col1 not col3, as the value "g" is present in col1 — Afsar, May 18 '13 at 07:35
Is it possible for you to use any scripting language? Coz, that'll to the job easily — draxxxeus, May 18 '13 at 07:38
@draxxxeus , m using it with php and also this task can be done with query itself — Afsar, May 18 '13 at 07:40
Why not to ask a question in a less cryptic way, using *real* field names and values, letting people whom you ask to help, more information? — Your Common Sense, May 18 '13 at 08:09

draxxxeus · Accepted Answer · 2013-05-18T10:16:49.630

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

In the first statement?? Check the statement after you have substituted the value — draxxxeus, May 18 '13 at 09:30

score -1 · Answer 2 · answered May 18 '13 at 08:13

-1

As you encrypted your question as much as possible, I can only guess.

this table smells of a bad design. And it should be like

 col  | value
 col1 | a
 col1 | b
 col2 | d

and so on.

than you can easily get your col out from value

answered May 18 '13 at 08:13

Your Common Sense

154,967
38
205
325

Exactly, that is the table design...for his problem. – user2361114 May 18 '13 at 08:16
do i really need to change my table design , now my table is pretty simple , i just need to get the column name thats it – Afsar May 18 '13 at 08:20

Get column name dynamically by Specific row value

2 Answers2

Linked

Related