CODEWITHSUNDEEP
sqloracle-databaselistagg
user1009073
user1009073

Reputation: 3238

Oracle Using ListAgg, distinct values, across multiple columns

for Oracle 12c... I have a table of line items sold to a company. This table has a 3-tier level hierarchy of the rep who sold to this company. One of the columns is the company name. I need help writing the SQL to generate a comma separated, unique list of the names of ALL the people, across all three columns, across all rows sold to this company. For an example...

CompanyName  Rep      Manager     GVP
-----------  -------  --------   --------
Sears        Bob      Tim        Frank
Sears        Jack     Tim        Frank
Ace          Scott    Chris      Bill

When I look at Sears, the SQL should return 'Bob, Jack, Tim, Frank'. The ORDER of the names does NOT matter, only that they are unique, and that they include names from all 3 fields. I would assume that this is a type of ListAgg query, but could be wrong...

Upvotes: 0

Views: 6489

Answers (3)

MT0
MT0

Reputation: 168671

Use the UNPIVOT operator (it will only do a single table scan whereas using UNION will typically do one table scan for each SELECT in the unioned statement):

Oracle Setup:

CREATE TABLE table_name ( CompanyName, Rep, Manager, GVP ) AS
SELECT 'Sears', 'Bob',   'Tim',   'Frank' FROM DUAL UNION ALL
SELECT 'Sears', 'Jack',  'Tim',   'Frank' FROM DUAL UNION ALL
SELECT 'Ace',   'Scott', 'Chris', 'Bill'  FROM DUAL;

Query:

SELECT CompanyName,
       LISTAGG( Name, ',' ) WITHIN GROUP ( ORDER BY Name ) AS Names
FROM   (
  SELECT DISTINCT
         CompanyName,
         Name
  FROM   table_name
  UNPIVOT( name FOR typ IN ( Rep, Manager, GVP ) )
)
GROUP BY CompanyName;

Output:

COMPANYNAME NAMES
----------- ------------------
Ace         Bill,Chris,Scott
Sears       Bob,Frank,Jack,Tim

Upvotes: 2

Samuel Renold
Samuel Renold

Reputation: 302

This SQL should do the trick:

select listagg(p, ', ') within group (order by p) from (
  select rep p
  from   your_table
  union
  select manager p
  from   your_table
  union
  select gvp p
  from   your_table);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You need to unpivot the data (to remove duplicates) and then reaggregate it:

select companyname, listagg(person, ',') within group (order by person) as persons
from ((select companyname, repfrom as person t) union
      (select companyname, manager from t) union
      (select companyname, gvp from t)
     ) t
group by companyname;

Upvotes: 2

Related Questions