How to put data in line in Oracle using Listagg-CodePudding

Im currently have a question: Specify the departments which have >=2 employees, print out the list of departments’ employees right after each department. I have following tables

Table EMPLOYEE:

EmpNo number CONSTRAINT PK_EmpNo PRIMARY KEY ,
EmpName varchar2(30),
Birthday DATE not null,
DeptNo number,
MgrNo varchar2(30) not null,
StartDate DATE not null,
Salary number(7,2) not null,
SYS_Level int,
Status int,
CONSTRAINT ck_SYS_Level CHECK  (SYS_Level > 0 AND SYS_Level < 8), 
CONSTRAINT ck_Status CHECK (Status >= 0 AND Status <= 2), 
Note varchar2(4000)

TABLE DEPARTMENT:

DeptNo int CONSTRAINT PK_DeptNo PRIMARY KEY,
DeptName varchar(30) not null,
Note varchar2(4000)

And I have insert data in those 2 Tables:

INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (1, 'Changed Dragon', TO_DATE('23-MAR-2000','DD-MON-YYYY'), 1, 'A02', TO_DATE('21-JUN-2020','DD-MON-YYYY'), 400, 2, 0, '[email protected]');
INSERT INTO  EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, 
Status, Email)
VALUES (2, 'Anna Lily', TO_DATE('20-MAY-1999','DD-MON-YYYY'), 3, 'L05', TO_DATE('16-NOV-2021','DD-MON-YYYY'), 600, 3, 0, '[email protected]');
INSERT INTO  EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (3, 'Michael Gray', TO_DATE('14-MAR-2002','DD-MON-YYYY'), 2, 'C08', TO_DATE('08-JAN-2021','DD-MON-YYYY'), 100, 1, 1, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (4, 'Arthur Gray', TO_DATE('18-OCT-2002','DD-MON-YYYY'), 4, 'C08', TO_DATE('14-FEB-2022','DD-MON-YYYY'), 200, 2, 2, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (5, 'Tommy Gray', TO_DATE('24-NOV-2002','DD-MON-YYYY'), 5, 'L05', TO_DATE('11-NOV-2021','DD-MON-YYYY'), 400, 5, 1, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, 
Status, Email)
VALUES (6, 'Ada Thone', TO_DATE('19-MAR-1998','DD-MON-YYYY'), 6, 'C08', TO_DATE('06-MAR-2022','DD-MON-YYYY'), 600, 3, 0, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (7, 'The Rock', TO_DATE('05-JUL-1999','DD-MON-YYYY'), 7, 'A02', TO_DATE('15-SEP-2022','DD-MON-YYYY'), 800, 3, 0, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (8, 'Han Sara', TO_DATE('21-NOV-2002','DD-MON-YYYY'), 1, 'L05', TO_DATE('21-JUL-2022','DD-MON-YYYY'), 700, 5, 1, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (9, 'KIM Jisoo', TO_DATE('01-SEP-1996','DD-MON-YYYY'), 6, 'L05', TO_DATE('17-MAY-2021','DD-MON-YYYY'), 900, 1, 2, '[email protected]');

Data of Table DEPARTMENT:

INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 1, 'Tester');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 2, 'DevOps');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 3, 'Test Lead');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 4, 'CTO');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 5, 'Tester');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 6, 'Test Manager');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 7, 'Senior');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 8, 'DevOps');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 9, 'Tester');

Here is the code I have tried:

SELECT D.DeptName, listagg(EmpName, ' , ') WITHIN GROUP(ORDER BY EmpName) AS Emps FROM 
DEPARTMENT D
JOIN EMPLOYEE E ON (E.DeptNo = D.DeptNo)
GROUP BY D.DeptName
HAVING COUNT(*) >= 2;

Here is the result:

The problem is I want the result that show in each line, example the name KIM Jisoo will be below the name Ada Thone, this is the desired result that I want, although I have used CHR(10) but didnt work

Any suggestions? Tks.

CodePudding user response：

Well, it works in SQL*Plus:

SQL> select d.dname,
  2         listagg(e.ename, chr(10)) within group (order by e.ename) emps
  3  from emp e join dept d on e.deptno = d.deptno
  4  group by d.dname;

DNAME          EMPS
-------------- ------------------------------
ACCOUNTING     CLARK
               KING
               MILLER

RESEARCH       ADAMS
               FORD
               JONES
               SCOTT
               SMITH

SALES          ALLEN
               BLAKE
               JAMES
               MARTIN
               TURNER
               WARD


SQL>

I presume you use some GUI whose output "hides" what you did. If it is SQL Developer, then double-click anywhere in the result - you'll see a pencil-like button on the right hand side of that field. Click on it, and you'll see the "correct" result.

Alternatively, run code as a script (if such an output satisfies you). You might need to set some additional settings (such as column formatting, page size, line size, etc.). Read about the set SQL*Plus command in documentation.