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

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

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

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

fiddle

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

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:

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:

fiddle