I am writing a ORACLE select statement with LISTAGG. It works but when I was validating the data, I saw duplicates like 1:1,1:1,2:2,2:2.
I verified that the database do have duplicate. The query is for two tables in a one to many relationship. Below is a snippet of my query but I changed the column names etc..
how can I modified the following query to return unique values for each person?
Thanks
SELECT Table1.PERSON_ID,
Table1.FIRST_NAME,
Table1.MIDDLE_NAME,
Table1.LAST_NAME,
LISTAGG(case
when Table2.DESCRIPTION = '1' then '1:1'
when Table2.DESCRIPTION = '2' then '2:2'
when Table2.DESCRIPTION = '3' then '3:3'
when Table2.DESCRIPTION = '4' then '4:4'
when Table2.DESCRIPTION = '5' then '5:5'
when Table2.DESCRIPTION = '6' then '6:6'
when Table2.DESCRIPTION = '7' then '7:7'
when Table2.DESCRIPTION = '8' then '8:8'
when Table2.DESCRIPTION = '9' then '9:9'
else ''
end, ',')
WITHIN GROUP (ORDER BY Table2.DESCRIPTION )
FROM Table1
LEFT JOIN Table2
ON Table2.PERSON_ID = Table1.PERSON_ID
GROUP BY Table1.PERSON_ID,Table1.FIRST_NAME,Table1.MIDDLE_NAME, Table1.LAST_NAME
Assuming Table2
has PERSON_ID
as foreign key pointing to Table1
, what you need to do is to replace
FROM Table1
LEFT JOIN Table2
ON Table2.PERSON_ID = Table1.PERSON_ID
with
FROM Table1
LEFT JOIN
(SELECT DISTINCT PERSON_ID, DESCRIPTION FROM Table2) T2
ON T2.PERSON_ID = Table1.PERSON_ID
and then change Table2
to T2
in LISTAGG()
(including in the WITHIN GROUP (ORDER BY ...)
clause!)