deptno dname empname
10     ACCT    SCOTT   
10     ACCT    MILLER   
20     SALES   JOHN    
20     SALES   XYZ   
30     FINANCE AAA 

entity_type name/no
DEPT        10
EMP         SCOTT
EMP         MILLER
DEPT        20
EMP         JOHN
EMP         XYZ 

SQL> select decode(grouping(ename), 0, 'EMP', 'DEPT') entity_type,
  2         decode(grouping(ename), 0, ename, to_char(deptno)) "NAME/NO"
  3  from emp
  4  group by rollup(deptno, ename)
  5  having grouping(deptno) = 0
  6  order by deptno, decode(grouping(ename), 1, ' ', ename)
  7  /
ENTI NAME/NO
---- ----------------------------------------
DEPT 10
EMP  CLARK
EMP  KING
EMP  MILLER
DEPT 20
EMP  ADAMS
EMP  FORD
EMP  JONES
EMP  SCOTT
EMP  SMITH
DEPT 30
EMP  ALLEN
EMP  BLAKE
EMP  JAMES
EMP  MARTIN
EMP  TURNER
EMP  WARD

select  decode(grouping(ename),0,'EMP','DEPT') entity_type,
        decode(grouping(ename),0,ename,to_char(deptno)) "NAME/NO"
  from  emp
  group by grouping sets((deptno),(deptno,ename))
  order by deptno,
           grouping(ename) desc,
           ename
/

ENTI NAME/NO
---- ------------
DEPT 10
EMP  CLARK
EMP  KING
EMP  MILLER
DEPT 20
EMP  ADAMS
EMP  FORD
EMP  JONES
EMP  SCOTT
EMP  SMITH
DEPT 30

ENTI NAME/NO
---- ------------
EMP  ALLEN
EMP  BLAKE
EMP  JAMES
EMP  MARTIN
EMP  TURNER
EMP  WARD

17 rows selected.

SQL>