LISTAGG returning repeated values

Question

LISTAGG returning repeated values

Navigation

#1 by (0 votes)

0

Hello everyone. Good afternoon.

I have a query that I did concatenating multiple records using a given key.

However, it brings some repeated values as a result. Here is an example of a query:

SELECT PEH.PN,
LISTAGG (PEH.AC_TYPE, ',') WITHIN GROUP (ORDER BY PEH.AC_TYPE DESC) AS 
"EFETIVIDADE"
FROM
ODB.PN_EFFECTIVITY_HEADER PEH
GROUP BY PEH.PN;

What it brings as a result:

PN | Efetividade

39 | E190,E190
41 | E170
42 | E190,E190
43 | ATR42
44 | ATR72,ATR72

In case you need it to not repeat equal values (like E190, for example).

How do I handle these issues in Oracle Developer?

Thank you very much.

oracle sqldeveloper

asked by anonymous 13.09.2018 / 21:56

1 answer

Attachment with phpMailer does not arrive Problem of Schema Soap Laravel

score 0 · Answer 1

a solution and make a virtual table

SELECT PEH.PN,
LISTAGG (PEH.AC_TYPE, ',') WITHIN GROUP (ORDER BY PEH.AC_TYPE DESC) AS 
"EFETIVIDADE"
FROM\
(SELECT DISTINCT  PEH.PN,PEH.AC_TYPE
FROM
ODB.PN_EFFECTIVITY_HEADER PEH) PEH
GROUP BY PEH.PN;