Oracle - LISTAGG Examples

ARRAY Aggregate WITH Deduplication
===
SELECT
REGEXP_REPLACE('00.17,00.40,00.40,00.45,00.66,36.07,37.22,38.97,51.01,88.53,88.56,88.77,93.39,93.83,97.29,97.29,99.04,99.29,99.29'
    ,'([^,]*)(,\1)+($|,)'
    ,'\1\3')
FROM dual;


PROCEDURE Example
===
SELECT FCT_ENCOUNTER.FCT_ENCOUNTER_ID
, REGEXP_REPLACE(
    LISTAGG(DIM_ICD.ICD_CODE, ',') WITHIN GROUP (ORDER BY DIM_ICD.ICD_CODE)
    ,'([^,]*)(,\1)+($|,)'
    ,'\1\3')
    AS ICD_CODE_LISTAGG
FROM EDW.FCT_ENCOUNTER FCT_ENCOUNTER
INNER JOIN EDW.DIM_FACILITY DIM_FACILITY
    ON FCT_ENCOUNTER.FACILITY_ID = DIM_FACILITY.FACILITY_ID
INNER JOIN EDW.FCT_ENCOUNTER_PROCEDURE FCT_ENCOUNTER_PROCEDURE
    ON FCT_ENCOUNTER.FCT_ENCOUNTER_ID = FCT_ENCOUNTER_PROCEDURE.FCT_ENCOUNTER_ID
INNER JOIN EDW.DIM_ICD DIM_ICD
    ON FCT_ENCOUNTER_PROCEDURE.ICD_ID = DIM_ICD.ICD_ID
WHERE 1=1
  AND (DIM_FACILITY.FACILITY_CODE = 'XX'
  AND DIM_FACILITY.STATUS = 'Yes')
GROUP BY FCT_ENCOUNTER.FCT_ENCOUNTER_ID
;

BEFORE:
FCT_ENCOUNTER_ID                ICD_CODE
===                             ===
1234567                         00.17
1234567                         00.40
1234567                         00.40
1234567                         00.45
1234567                         00.66
1234567                         00.66

AFTER:
FCT_ENCOUNTER_ID                ICD_CODE_LISTAGG
===                             ===
1234567                         00.17,00.40,00.45,00.66


LISTAGG USING PARTITION BY
===

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date)
       OVER (PARTITION BY department_id) AS "Employees"
  FROM employees
  ORDER BY department_id;