How to get column name based on condition

Question

I have the following query that checks if columns are null

select 
    sum(case when Column_1 is null then 1 else 0 end) as Column_1, 
    sum(case when Column_2 is null then 1 else 0 end) as Column_2, 
    sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable 

It gives:

Column_1  Column_2  Column_3
0         1         0

I want to get the column names that have null values So my desired out put is:

Column_1
Column_3

How can I do that in Presto? It doesn't seem easy to get as out put column name returned from query.

Answer 1

I understand that you want your results on separate row rather than in a concatenated string.

If so, you can unpivot your existing resultset with unnest() and arrays;

select t2.key
from (
    select 
        sum(case when Column_1 is null then 1 else 0 end) as Column_1, 
        sum(case when Column_2 is null then 1 else 0 end) as Column_2, 
        sum(case when Column_3 is null then 1 else 0 end) as Column_3
    from TestTable 
) t1
cross join unnest(
    array['Column1', 'Column_2', 'Column_3'],
    array[Column1, Column_2, Column_3]
) t2 (key, value)
where t2.value = 0