deeejay

PostgreSQL - Aggregate Functions

Feb 16th, 2016
5,151
0
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Create json from select query
  2. ===
  3. SELECT json_agg(f) FROM dw.dim_facility f;
  4.  
  5. Create comma-delimited, single-quote enclosed value
  6. ===
  7. SELECT ARRAY_AGG(DISTINCT QUOTE_NULLABLE(sc.surg_case_nbr_formatted)) AS surg_case_nbr_formatted
  8. FROM ods.pic_surgical_case sc
  9. INNER JOIN ods.pic_surg_case_procedure scp
  10. ON sc.surg_case_id = scp.surg_case_id
  11. AND scp.active_ind = 1
  12. WHERE scp.surg_proc_cd = 450414963 --CASE CANCELLED
  13. AND DATE_PART('year', sc.create_dt_tm) > 2013
  14.  
  15. Results:
  16. {'99END-2014-1234','99END-2015-111','99END-2016-1111'}
  17.  
  18. Notes:
  19. Use RegEx replacement in "Replace in String" transformation step to remove brackets via ETL
  20. RegEx search = \{(.*)\}
Advertisement
Comments
  • User was banned
Add Comment
Please, Sign In to add comment