How to write a case statement showing null to a value and vice versa Snowflake

Question

I have a table like

ID	COUNTRY_1	COUNTRY_2
5	USA	CAN
6	NULL	USA
7	MEX	NULL

I am writing a query such that I can show what the values change to within a singular column. For example I am trying to create a table like

ID	CHANGES
5	USA -> CAN
6	NULL -> USA
7	MEX -> NULL

I wrote my query like

    SELECT ID, CASE WHEN COUNTRY_1 <> COUNTRY_2 THEN COUNTRY_1 || '->' || COUNTRY_2 ELSE '' END   
    AS CHANGES FROM MY_TABLE

However this resulted in a table like

ID	CHANGES
5	USA -> CAN
6
7

which doesnt show a change from a value to NULL or NULL to a value, is there a way I can show these changes as well in my tables in addition to a non-null value to another non-null value? Thanks!

Answer 1

You can convert nulls to 'NULL' then apply your query :

WITH CTE as (
   SELECT ID, CASE WHEN COUNTRY_1 IS NOT NULL THEN COUNTRY_1 ELSE 'NULL' END AS COUNTRY_1,
              CASE WHEN COUNTRY_2 IS NOT NULL THEN COUNTRY_2 ELSE 'NULL' END AS COUNTRY_2
   FROM MY_TABLE
   
)
SELECT ID, CASE WHEN COUNTRY_1 <> COUNTRY_2 
                THEN COUNTRY_1 || '->' || COUNTRY_2 
                ELSE '' END  AS CHANGES 
FROM CTE;