I am trying to make a query in mysql to get any column that has a specific value for a specific row. In Mysql we can get rows based on any specific value of a column.
I have a table like this:
+----+------------+------------+---------------+---------------+---------+----------------+--------- | 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 that when I select a row based on MSISDN, it should return all the column names of that row with a fixed value (e.g. 1).
Therefore, in the above table, MSISDN = 9944994488 should return
MissedCall AdvanceCredit SuitablePackId
What I tried is:
SELECT COLUMN_NAME as names FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bi' AND TABLE_NAME = 'useranalysisresult'
This returns the column names of the table. But how to get column name with specific value. Thanks for your help in advance.
P粉7655701152023-12-12 00:03:09
The comment is too long.
SQL queries return a fixed set of columns. You cannot change settings on a row basis. You can use prepared statements to do what you want, although this may seem like a mysterious approach.
You can return a column containing values concatenated together. Something like this:
select concat_ws(',', (case when MissedCall = 1 then 'Missed Call' end), (case when SponsoredCall = 1 then 'Sponsored Call' end), . . . ) from useranalysisresult;
This will generate a list in a single column of the flags that are set.