CODEWITHSUNDEEP
oracle-databaseplsql
Sandy
Sandy

Reputation: 13

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

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?

Upvotes: 1

Views: 675

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

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";

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

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

Upvotes: 0

Related Questions