Search code examples
mysqlselectnotnull

Selecting column names which have values

Consider the following table:

+-----------+---+------+---+
| Telephone | A |  B   | C |
+-----------+---+------+---+
|  12345    | x | NULL | y |
|  32456    | z | NULL | a |
+-----------+---+------+---+

Assuming that this table is not limited to 4 columns (i.e., I don't know the column count since it varies) how do I select the column names which have not null? (In this example, I want the results to be A and C since they're populated.)

Solution

  • If you want the column names, you can use an aggregation query with case:

    select concat_ws(',',
                 (case when count(telephone) > 0 then 'telephone' end),
                 (case when count(A) > 0 then 'A' end),
                 (case when count(B) > 0 then 'B' end),
                 (case when count(C) > 0 then 'C' end)
                ) as ColumnsWithVowels
from t;