Exclude Duplicates in LISTAGG

Question

I'm using the aggregate function LISTAGG() to put multiple matches into a single cell, seperated by a comma. The problem is, on some rows, I get duplicate values, e.g.

Current Output

Permit #        Inspection
B2023001        Air, Air, Air, Water
B2023002        Gas, Air, Gas
B2023003        Water, Water, Water

Desired Output

Permit #        Inspection
B2023001        Air, Water
B2023002        Gas, Air
B2023003        Water

Query

SELECT
    B1PERMIT.B1_ALT_ID AS "Permit #",
    LISTAGG( G6ACTION.G6_ACT_TYP, ', ' ) WITHIN GROUP ( ORDER BY G6ACTION.G6_ACT_TYP ) AS "Inspection"
FROM
    B1PERMIT B1PERMIT
INNER JOIN GPROCESS_HISTORY GPROCESS_HISTORY
    ON B1PERMIT.B1_PER_ID1 = GPROCESS_HISTORY.B1_PER_ID1
        AND B1PERMIT.B1_PER_ID3 = GPROCESS_HISTORY.B1_PER_ID3
INNER JOIN G6ACTION G6ACTION
    ON B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
    AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
    AND ( G6ACTION.G6_ACT_TYP LIKE '%Final%' AND G6ACTION.G6_STATUS = 'Approved' )

    GROUP BY B1PERMIT.B1_ALT_ID
    ORDER BY B1PERMIT.B1_ALT_ID DESC

I tried using DISTINCT but that doesn't work since my Oracle version is too old (12.1):

 LISTAGG( DISTINCT G6ACTION.G6_ACT_TYP, ', ' ) WITHIN GROUP ( ORDER BY G6ACTION.G6_ACT_TYP ) AS "Inspection"

What other options do I have besides using DISTINCT in LISTAGG()?

Answer 1

You can simply get a DISTINCT on your group key and G6_ACT_TYPE and in an outer query block do the LISTAGG:

   SELECT B1_ALT_ID AS "Permit #",
          LISTAGG( G6_ACT_TYP, ', ' ) WITHIN GROUP ( ORDER BY G6_ACT_TYP ) AS "Inspection"
     FROM (SELECT DISTINCT
                  B1PERMIT.B1_ALT_ID,
                  G6_ACT_TYP
              FROM
                  B1PERMIT B1PERMIT
              INNER JOIN GPROCESS_HISTORY GPROCESS_HISTORY
                  ON B1PERMIT.B1_PER_ID1 = GPROCESS_HISTORY.B1_PER_ID1
                      AND B1PERMIT.B1_PER_ID3 = GPROCESS_HISTORY.B1_PER_ID3
              INNER JOIN G6ACTION G6ACTION
                  ON B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
                  AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
                  AND ( G6ACTION.G6_ACT_TYP LIKE '%Final%' AND G6ACTION.G6_STATUS = 'Approved' ))
    GROUP BY B1_ALT_ID
    ORDER BY B1_ALT_ID DESC