you can make use of list tag to have multiple rows:

  -- Use := for assignment in LET
        LET textMessage varchar := (
            SELECT LISTAGG('Table: ' || table_name || ' | Duplicates: ' || value, '\n')
            FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
            WHERE METRIC_NAME = 'CHECK_DUPLICATE_ID_FLAG'
              AND MEASUREMENT_TIME > DATEADD('DAY', -1, CURRENT_TIMESTAMP())
              AND VALUE > 0
        );

output is as follows:

enter image description here