Snowflake - Concatenating fields nicely

Sometimes, you want to concat a few fields together and have a separator, but you only want the separator when the next field is not empty and not blank

ARRAY_CONSTRUCT creates an array from the fields 

ARRAY_COMPACT removes blanks and "undefined"
ARRAY_TO_STRING converts the array to a single string, with a separator

SELECT  PERSONID ,
        ARRAY_TO_STRING(
          ARRAY_COMPACT(
            ARRAY_CONSTRUCT(
              NULLIF(ADRLINE1,  ''),
              NULLIF(ADRLINE2, ''),
              NULLIF(ADRLINE3,  '')
            )
          ),
          ', '
        ) AS ADDRESS
FROM PERSONTABLE ;