Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
5 rows affected

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)
);


EMPID
CNT
EMP1
EMP2


EM2
2
EM3
EM4


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';


EMPID
CNT
INDEX_OF_EMP
EMP


EM2
2
1
EM3

EM2
2
2
EM4