LISTAGG returning repeated values

Asked

Viewed 161 times

0

Hello, everybody. Good afternoon.

I have a query that concatenated several record using a certain key.

However it brings some repeated values as a result. Below 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;

Which brings as a result:

PN | Efetividade

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

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

How I Treat These Incidences in Oracle Developer?

Thank you very much.

oracle sqldeveloper

  • Hi, Luan, hello, Luan! First I would advise you to do the query without the listagg, there should probably be duplicate values, so you would have to treat in your query not to repeat these values. Simulating the above problem: http://sqlfiddle.com/#! 4/74fa1/3/0

  • Hello, friend. In the table the values were not duplicated. I checked before doing the LISTAGG. The friend solution below creating a virtual table worked! Thank you so much for your contribution! Hug!!

1 answer

0

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;

  • Friend, thank you very much. It worked perfectly. Big hug!!

Browser other questions tagged oracle sqldeveloper

You are not signed in. Login or sign up in order to post.