Oracle SQL Listagg remove duplicates with case statement conditions

Question

I am trying to show repeated column values with comma separated list by using listagg but getting error as "Not a single group by function". Hope I get some help.

Below is the DDL script with insert statements and data:

DROP TABLE dept CASCADE CONSTRAINTS;

DROP TABLE myrole CASCADE CONSTRAINTS;

DROP TABLE person CASCADE CONSTRAINTS;

DROP TABLE person_role CASCADE CONSTRAINTS;
   
CREATE TABLE dept (
    id    INTEGER NOT NULL,
    dept  VARCHAR2(50 CHAR)
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    1,
    'Operations'
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    2,
    'Research'
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    3,
    'Accounts'
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    4,
    'Sales'
);

ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY ( id );

CREATE TABLE myrole (
    id    INTEGER NOT NULL,
    role  VARCHAR2(50 CHAR)
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    1,
    'JJJ'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    2,
    'Auth'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    3,
    'AAA'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    4,
    'MMM'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    5,
    'KKK'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    6,
    'BBB'
);

ALTER TABLE myrole ADD CONSTRAINT myrole_pk PRIMARY KEY ( id );

CREATE TABLE person (
    id      INTEGER NOT NULL,
    person  VARCHAR2(50 CHAR)
);

INSERT INTO person (
    id,
    person
) VALUES (
    1,
    'John'
);

INSERT INTO person (
    id,
    person
) VALUES (
    2,
    'Scott'
);

INSERT INTO person (
    id,
    person
) VALUES (
    3,
    'Ruth'
);

INSERT INTO person (
    id,
    person
) VALUES (
    4,
    'Smith'
);

INSERT INTO person (
    id,
    person
) VALUES (
    5,
    'Frank'
);

INSERT INTO person (
    id,
    person
) VALUES (
    6,
    'Martin'
);

INSERT INTO person (
    id,
    person
) VALUES (
    7,
    'Blake'
);

ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY ( id );

CREATE TABLE person_role (
    id         INTEGER NOT NULL,
    person_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    dept_id    INTEGER
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    1,
    1,
    1,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    2,
    2,
    2,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    3,
    2,
    4,
    1
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    4,
    2,
    4,
    2
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    5,
    3,
    1,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    6,
    3,
    5,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    7,
    4,
    3,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    8,
    5,
    6,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    9,
    6,
    6,
    3
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    10,
    6,
    6,
    2
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    11,
    6,
    2,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    12,
    7,
    6,
    4
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    13,
    7,
    6,
    4
);

ALTER TABLE person_role ADD CONSTRAINT person_role_pk PRIMARY KEY ( id );

ALTER TABLE person_role
    ADD CONSTRAINT person_role_myrole_fk FOREIGN KEY ( myrole_id )
        REFERENCES myrole ( id );

ALTER TABLE person_role
    ADD CONSTRAINT person_role_person_fk FOREIGN KEY ( person_id )
        REFERENCES person ( id );

CREATE SEQUENCE dept_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER dept_tr BEFORE
    INSERT ON dept
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := dept_seq.nextval;
END;
/

CREATE SEQUENCE myrole_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER myrole_tr BEFORE
    INSERT ON myrole
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := myrole_seq.nextval;
END;
/

CREATE SEQUENCE person_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER person_tr BEFORE
    INSERT ON person
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := person_seq.nextval;
END;
/

CREATE SEQUENCE person_role_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER person_role_tr BEFORE
    INSERT ON person_role
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := person_role_seq.nextval;
END;
/

By using below query that @Koen Lostrie provided and by adding columns I need, I get output as shown:

SELECT p.person, r.role as myrole, d.dept,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id
    left join dept d on d.id = pr.dept_id

Output from query:

+--------+--------+------------+----------+
| PERSON | MYROLE |    DEPT    | MYACCESS |
+--------+--------+------------+----------+
| John   | JJJ    |            |          |
| Scott  | Auth   |            | Remove   |
| Scott  | MMM    | Operations |          |
| Scott  | MMM    | Research   |          |
| Ruth   | JJJ    |            |          |
| Ruth   | KKK    |            |          |
| Smith  | AAA    |            | Add      |
| Frank  | BBB    |            | Add      |
| Martin | AAA    | Accounts   |          |
| Martin | AAA    | Research   |          |
| Martin | Auth   |            | Remove   |
| Blake  | BBB    | Sales      |          |
| Blake  | BBB    | Sales      | Add      |
+--------+--------+------------+----------+

Now I want to show DEPT column values comma separated based on PERSON and MYROLE columns and the output expected is shown below:

+--------+--------+---------------------+----------+
| PERSON | MYROLE |        DEPT         | MYACCESS |
+--------+--------+---------------------+----------+
| John   | JJJ    |                     |          |
| Scott  | Auth   |                     | Remove   |
| Scott  | MMM    | Operations,Research |          |
| Ruth   | JJJ    |                     |          |
| Ruth   | KKK    |                     |          |
| Smith  | AAA    |                     | Add      |
| Frank  | BBB    |                     | Add      |
| Martin | AAA    | Accounts,Research   |          |
| Martin | Auth   |                     | Remove   |
| Blake  | BBB    | Sales               | Add      |
+--------+--------+---------------------+----------+

I added listagg to existing query but getting error

SELECT p.person, r.role as myrole,  
listagg(d.dept, ', ') within group (order by d.dept) as dept,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id
    left join dept d on d.id = pr.dept_id

getting not a single group by error. Not sure how to fix. Appreciate any help.

Thanks,
Richa

Answer 1

LISTAGG is an aggregate function. If you apply it to a column, then you need to specify in the query what columns you're grouping by. Typically that is all the columns that don't have an aggregate function. I didn't test since there is no sample data for the dept table nor the person_roles table but this is probably the issue

SELECT p.person, r.role as myrole, listagg(d.dept, ', ') within group (order by d.dept) as dept_list,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id 
left join dept d on d.id = pr.dept_id
GROUP BY
  p.person, 
  r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END
ORDER BY p.person