PL/SQL Developer remove duplicate entry but keep relevant column data

Question

Hi I have a query in which I need to remove or format output display of a code. Please see below:

Query:

select rfc."NUMBER",
ag.current_pending_groups
from smspadm.cm3rm1 rfc, smspadm.tv_approval_vw appr, smspadm.approvala1 ag
Order By rfc."NUMBER"

Current result:

NUMBER  CURRENT_PENDING_GROUPS

C301609 Comm/AT&T Vert DCAB
C302023 Collections DCAB
C302023 Comm/AT&T Vert DCAB
C302023 Fin-D2C-DNB DCAB
C302023 Interactive Recording DCAB
C302103 Collections DCAB
C302103 Wintel Server DCAB
C302103 Fin-D2C-DNB DCAB
C301813 GTHRA Vert DCAB
C301813 Tech Vert DCAB

Desired result: Either:

NUMBER  CURRENT_PENDING_GROUPS

C301609 Comm/AT&T Vert DCAB
C302023 Collections DCAB, Comm/AT&T Vert DCAB, Fin-D2C-DNB DCAB
C302103 Collections DCAB, Wintel Server DCAB, Fin-D2C-DNB DCAB
C301813 GTHRA Vert DCAB, Tech Vert DCAB

Or it should be like:

NUMBER  CURRENT_PENDING_GROUPS

C301609 Comm/AT&T Vert DCAB
C302023 Collections DCAB
        Comm/AT&T Vert DCAB
        Fin-D2C-DNB DCAB
        Interactive Recording DCAB
C302103 Collections DCAB
        Wintel Server DCAB
        Fin-D2C-DNB DCAB
C301813 GTHRA Vert DCAB
        Tech Vert DCAB

What query should I use to get this result?

You may find an answer [here](http://stackoverflow.com/questions/1441971/oracle-normalized-fields-to-csv-string) , [here](http://stackoverflow.com/questions/4686543/sql-to-concatenate-column-values-from-multiple-rows), [here](http://stackoverflow.com/questions/10997854/how-can-i-return-a-csv-string-from-pl-sql-table-type-in-oracle), [here](http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row), [here](http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle) — A.B.Cade, Mar 18 '13 at 15:30

Mohsen Heydari · Answer 1 · 2013-03-19T17:49:43.387

1

SELECT rfc."NUMBER",
  LISTAGG(ag.current_pending_groups, ', ')
   WITHIN GROUP (ORDER BY rfc."NUMBER") "CURRENT_PENDING_GROUPS"
   FROM smspadm.cm3rm1 rfc, smspadm.tv_approval_vw appr, smspadm.approvala1 ag
   GROUP BY rfc."NUMBER";


SELECT rfc."NUMBER",
   wm_concat(ag.current_pending_groups)
   FROM smspadm.cm3rm1 rfc, smspadm.tv_approval_vw appr, smspadm.approvala1 ag
   GROUP BY rfc."NUMBER";

edited Mar 19 '13 at 17:49

answered Mar 18 '13 at 18:04

Mohsen Heydari

7,256
4
31
46

Is the above solution doesn't work for you? if so what exception you are getting? – Mohsen Heydari Mar 19 '13 at 17:08
Nope, it stops me at "WITHIN GROUP" with error "ORA-00923: FROM Keyword not found where expected" – Sandy Mar 19 '13 at 17:40

Egor Skriptunoff · Accepted Answer · 2013-03-18T15:26:03.190

0

First variant

select 
  rfc."NUMBER",
  wm_concat(ag.current_pending_groups)  -- for 10g
  -- listagg(ag.current_pending_groups,',') within group (order by ag.current_pending_groups)  -- for 11g
  as CURRENT_PENDING_GROUPS
from smspadm.cm3rm1 rfc, smspadm.tv_approval_vw appr, smspadm.approvala1 ag
group by rfc."NUMBER"
Order By rfc."NUMBER"

Second variant

select
  case when
    row_number() over (partition by rfc."NUMBER" order by ag.current_pending_groups) = 1
  then
    rfc."NUMBER"
  end as "NUMBER",
  ag.current_pending_groups
from smspadm.cm3rm1 rfc, smspadm.tv_approval_vw appr, smspadm.approvala1 ag
Order By rfc."NUMBER", ag.current_pending_groups

edited Mar 18 '13 at 15:26

answered Mar 18 '13 at 15:20

Egor Skriptunoff

23,359
2
34
64

3

This works, but is unsupported by Oracle (see MOS 1336219.1), so shouldn't really be recommended – Chris Saxon Mar 18 '13 at 15:25
1

@ChrisSaxon - Don't be afraid of WM_CONCAT :-) It works just fine. – Egor Skriptunoff Mar 18 '13 at 15:31
I am sory if I forgot to mention that we are using Oracle 10G and PL/SQL 8.0.1 – Sandy Mar 19 '13 at 16:46

PL/SQL Developer remove duplicate entry but keep relevant column data

2 Answers2