Pasted

Create json from select query
===
SELECT json_agg(f) FROM dw.dim_facility f;
 
Create comma-delimited, single-quote enclosed value
===
SELECT ARRAY_AGG(DISTINCT QUOTE_NULLABLE(sc.surg_case_nbr_formatted)) AS surg_case_nbr_formatted
FROM ods.pic_surgical_case sc
INNER JOIN ods.pic_surg_case_procedure scp
ON sc.surg_case_id = scp.surg_case_id
AND scp.active_ind = 1
WHERE scp.surg_proc_cd = 450414963 --CASE CANCELLED
AND DATE_PART('year', sc.create_dt_tm) > 2013
 
Results:
{'99END-2014-1234','99END-2015-111','99END-2016-1111'}
 
Notes:
Use RegEx replacement in "Replace in String" transformation step to remove brackets via ETL
RegEx search = \{(.*)\}