John John - 4 months ago 164
SQL Question

Oracle Select JOIN Listagg. Duplicates in the output. Using Distinct or other

I have been working on this query to between two tables (one to many) and append all the fields together into one string using LISTAGG. I noticed that the outputs have duplicates values and I found that there are duplicates records in the table. How do I use distinct or another technique to remove the duplicate.

Here is my query

SELECT Table2.PersonId,
LISTAGG(case Table2.BookTitleDesc
when 'Value1' then '1'
when Value2' then '2'
when 'Value3' then '3'
when 'Value4' then '4'

end, ':')
WITHIN GROUP (ORDER BY Table2.BookTitleDesc )
FROM Table1
INNER JOIN Table2
ON Table1.PersonId = Table2.PersonId
GROUP BY Table2.PERSON_ID


I am getting output like 1:1:2:3:4. Ideally the BookTitleDesc should be unique but the tables has dups. In my CASE.. WHEN statement, it will be really long. I
have a lot to filter and I saw there were many dups. How can I use DISTINCT or some other technique to avoid dups in my output?

Thanks

Answer

Can't you just run it through a SELECT DISTINCT before aggregating?

SELECT s.PersonId, 
LISTAGG(case s.BookTitleDesc 
        when 'Value1' then '1'
            when 'Value2' then '2'
            when 'Value3' then '3'
            when 'Value4' then '4'

 end, ':') 
WITHIN GROUP (ORDER BY s.BookTitleDesc )  
FROM (
    SELECT DISTINCT Table2.PersonId,
        Table2.BookTitleDesc
    FROM Table1
    INNER JOIN Table2 
    ON Table1.PersonId = Table2.PersonId
    ) s
GROUP BY s.PERSON_ID
Comments