self join query which gives count of join record and their ids

Question

I have a table on oracle database for exp. EMPLOYEE which has following data:

ID	EMPID	NAME	manager
1	EM1	ana	EM3
2	EM2	john
3	EM3	ravi	EM2
4	EM4	das	EM2
5	EM5	michael	EM3

empid is a unique column and Manager column store empid of manage, so now I have to select a manager id and count of employee under them and empid of then in one row like

EMPID	COUNT	EMP1	EMP2
ME2	2	EM3	EM4

What I was able to achieve:

select 
  e2.empid as manager,
  e2.name manger_name,
  count(*) over (partition by e2.empid) as employee_count,
  e1.empid as employee,
  e1.name as employee_name
  from employee e1 left join employee e2 on e1.manager = e2.empid

MANAGER	MANGER_NAME	EMPLOYEE_COUNT	EMPLOYEE	EMPLOYEE_NAME
EM2	john	2	EM4	das
EM2	john	2	EM3	ravi
EM3	ravi	2	EM5	michael
EM3	ravi	2	EM1	ana
null	null	1	EM2	john

fiddle

can anyone suggest how can I achieve this result in oracle sql

Answer 1

In SQL (in all dialects, not just Oracle's) you need to know how many columns there are going to be in the output; therefore it is impossible to dynamically generate columns for an unknown number of employees under each manager.

If you only want to show 2 employees then you can use:

SELECT *
FROM   (
  SELECT manager AS empid,
         empid AS emp,
         COUNT(*) OVER (PARTITION BY manager) AS cnt,
         ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS rn
  FROM   employee
  WHERE  manager = 'EM2'
)
PIVOT (
  MAX(emp)
  FOR rn IN (1 AS emp1, 2 AS emp2)
);

Which, for the sample data:

CREATE TABLE employee (ID, EMPID, NAME, manager) AS
SELECT 1, 'EM1', 'ana',     'EM3' FROM DUAL UNION ALL
SELECT 2, 'EM2', 'john',    NULL  FROM DUAL UNION ALL
SELECT 3, 'EM3', 'ravi',    'EM2' FROM DUAL UNION ALL
SELECT 4, 'EM4', 'das',     'EM2' FROM DUAL UNION ALL
SELECT 5, 'EM5', 'michael', 'EM3' FROM DUAL;

Outputs:

EMPID	CNT	EMP1	EMP2
EM2	2	EM3	EM4

---

If you want all the employees of a manager (and a count of the total) then use rows, not columns:

SELECT manager AS empid,
       COUNT(*) OVER (PARTITION BY manager) AS cnt,
       ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS index_of_emp,
       empid AS emp
FROM   employee
WHERE  manager = 'EM2';

Which outputs:

EMPID	CNT	INDEX_OF_EMP	EMP
EM2	2	1	EM3
EM2	2	2	EM4

fiddle

Answer 2

Try this:

select manager, count(empid) as count, 
listagg(empid, ',' ) within group (order by empid) as employees 
from employee
where (manager is not null)
group by manager order by manager;

fiddle

For readers using >=18c: in case JSON is fine, you can use:

select manager, count(empid) as count, 
json_objectagg(key 'emp'||to_char(rn) value empid) as employees 
from
  (select manager, empid, 
     row_number() over (partition by manager order by empid) as rn
   from employee
   where (manager is not null))
group by manager order by manager;

fiddle