You must Sign In to post a response.
  • Category: SQL Server

    SQL Query /stored procedure to get complete organization hierarchy detail

    I tried and searched the solution for the below problem statement but unfortunately i did't get the exact output,I need to display the complete organizational hierarchy in the below format,i tried with using CTE with UNION ALL but still i'm unable to get the exact output,please help me its bit urgent. Thanks in Advance.
    EMPNAME EMPID EMP DESIGNATION
    ------------------------------------
    EMP1 00001 CEO
    EMP2 00009 MANAGER 1
    EMP3 00005 TL1
    EMP4 00003 SSD1
    EMP5 00006 SD1
    EMP6 00008 TL2
    EMP7 00020 SSD2
    EMP8 25 SD2
    EMP9 00030 TL3
    EMP10 00017 MANAGER2
    EMP11 00029 TL4
    EMP12 00045 SSD3
    EMP13 00060 SSD4
    EMP14 00070 SD3
    EMP15 00060 SD4
    EMP16 00090 SD5
    EMP17 00055 SD6
    CONTI… CONTI… CONTI…
    .. …. ..
  • #761598

    Delete Attachment

  • #761602
    I tried with my Majic Wand to see your table structures from where you tried to derive the Hierarchy but it didn't bring me your tables. Could you please Post your table structures with some sample data so that i can try my best to derive you the hierarchy.
    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...

  • #761607
    what columns do you have in your database. do you have some level field which indicates at what level particular employee is employed.
    Miss. Jain
    Microsoft Certified Technology Specialist in .Net

  • #761630
    1. This table only contain the details of EMP.
    2. Create one more table like Manager_Emp(TopHirar,BottomHirar)
    3. Add the data like (CEO, Manager1)
    (CEO, Manager2)
    (Manager1 , TL1)
    4. Then you can try. It will work.

    By Nathan
    Direction is important than speed


Sign In to post your comments