-- INIT database

CREATE TABLE testbed (

  EID INT,

  TITLE VARCHAR2(100),

  USER_GROUP VARCHAR2(255)

);

​

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (1,'Secretary', 'Users');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (1,'Secretary', 'Admin');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (1,'Secretary', 'Guest');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (2,'Janitor', 'Users');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (2,'Janitor',    'Guest');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (3,'Secretary', 'Admin');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (3,'Secretary', 'Users');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (4,'Janitor', 'Admin');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (4,'Janitor', 'Users');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (5,'Janitor', 'Admin');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (5,'Janitor', 'Users');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (6,'Secretary', 'Guest');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (6,'Secretary', 'Admin');

INSERT INTO testbed(EID, TITLE, USER_GROUP) VALUES (6,'Secretary', 'Users');

-- QUERY database

with cte as (

select 

 title, 

 user_group, 

 count(eid) as people

from testbed

group by title, user_group

)

select 

 title, 

 listagg(user_group, ', ') within group (order by user_group) as groups

from cte

where people > 1

group by title;