SQL & PL/SQL

Hi,

I have a query which displays the hierarchical relation between department and employee.

WITH dept_emps AS

     (SELECT d.dept_id ID, d.dept_name nm, NULL emp_id

        FROM dept d

      UNION ALL

      SELECT e.dept_id ID, e.ename,

             e.empno emp_id

        FROM emp e)

SELECT   CASE

            WHEN emp_id IS NOT NULL

               THEN '    ' || nm

            ELSE nm

         END nm

    FROM dept_emps

ORDER BY ID, emp_id NULLS FIRST;

NM

SALES

    SMITH

    ADAMS

CD

    JONES

    SCOTT

DBA

    FORD

    CLARK

But my required output is:

Dept            Employee

SALES        SMITH

                    ADAMS

CD               JONES

                    SCOTT

DBA             FORD

                     CLARK

Can anyone please help how to achieve this?

Regards,

Nivetha Ramnath