select Column names having a fixed value for any particular row

Question

I am trying to make a query in mysql to get any column which has a particular value for one specific row. In Mysql we can get rows based upon any specific value of a column.

I have a table like :

+----+------------+------------+---------------+---------------+---------+----------------+---------
| ID | MSISDN     | MissedCall | SponsoredCall | AdvanceCredit | ACvalue | SuitablePackId | AutoTimeStamp       |
+----+------------+------------+---------------+---------------+---------+----------------+---------------------+
|  1 | 9944994488 |          1 |             0 |             1 |       0 |              1 | 2014-09-18 10:42:55 |
|  4 | 9879877897 |          0 |             1 |             0 |       0 |              2 | 2014-09-18 10:42:55 |
+----+------------+------------+---------------+---------------+---------+----------------+---------------------+

What i need is when i select a row based upon MSISDN , it should return all column names for that row whose value is fix (say 1).

So in above table for MSISDN = 9944994488 it should return

MissedCall 
AdvanceCredit 
SuitablePackId 

What i have tried is :

SELECT COLUMN_NAME as names 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'bi' 
AND TABLE_NAME = 'useranalysisresult'

This returns me column names of table. But how to get column names with specific value. Thanks for help in advance.

Answer 1

This is too long for a comment.

A SQL query returns a fixed set of columns. You cannot change the set depending on the row. You could do what you want using a prepared statement, although that would seem like an arcane approach.

You could return a single column with values concatenated together. Something like:

select concat_ws(',',
                 (case when MissedCall = 1 then 'Missed Call' end),
                 (case when SponsoredCall = 1 then 'Sponsored Call' end),
                 . . .
                )
from useranalysisresult;

This would produce a list in a single column of the flags being set.

Answer 2

Try the below one. Here replace the YOUR_SCHEMA with your actual schema name and YOUR_TABLENAME with your actual table name:

select column_name from information_schema.columns  
where  YOUR_SCHEMA = //any condition 
and  table_name='YOUR_TABLENAME';