SQL question on hierarchical data

From: Sunil Godithi <sunil_at_ameya.com>
Date: 1998/10/06
Message-ID: <dFvrDOV89GA.64@newstoo.hiwaay.net>#1/1

hi,
I have recently switched to SQL Server from oracle world and I am having problem with Select statement.

I have a table called emp with structure like: EMPNO ENAME MGR MGR is the EMPNO of the manager. I need to get a list of employees like this (select statement is Oracle specific, I need something similar for SQLServer)...
SQL> l
  1 select level

  2        , ename
  3        , empno
  4        , mgr
  5     from emp

  6 connect by prior empno = mgr
  7* start with job = 'PRESIDENT'
SQL> /

    LEVEL     ENAME            EMPNO         MGR
    ---------      ----------             ---------             ---------
        1             KING              7839
        2           JONES           7566      7839
        3           SCOTT           7788      7566
        4          ADAMS           7876      7788
        3          FORD            7902      7566
        4         SMITHXX         7369      7902
        2         BLAKE           7698      7839
        3         ALLEN           7499      7698
        3         WARD            7521      7698
        3         MARTIN          7654      7698
        3         TURNER          7844      7698
        3         JAMES           7900      7698
        2         CLARK           7782      7839
        3         MILLER          7934      7782

14 rows selected.

Any help is appreciated..

Sunil Received on Tue Oct 06 1998 - 00:00:00 CDT