count - How would I transpose SQL: Sum Case Script in Redshift and is it possible to Join to information table?

Question

I currently have a script that counts the number of null records in each column. The script is below

select  sum(case when id is null then 1 else 0 end)id , sum(case when createddate is null then 1 else 0 end)createddate from schema_name.Table_name

The results come over like

|id|createddate|
|0 |    5      |

Is there a way to transpose this to get the results like

|id          | 0 |
|created date| 5|

Lastly, if this is possible would I be able to join this to the information table (SVV_TABLE_INFO)

Answer 1

You can use union all:

select 'id', count(*)
from t
where id is null
union all
select 'createddate', count(*)
from t
where createddate is null;