troubleshooting Question

SQL - Ordering Supervisor Hierarchy

Avatar of nightshadz
nightshadzFlag for United States of America asked on
Microsoft SQL Server
2 Comments1 Solution103 ViewsLast Modified:
I have a supervisor table with three columns which looks similar to the one below. I need to reformat the reporting structure in a specific format which is defined below. It basically starts out at the top level and any direct report to that person is -1.  Hope this makes sense. Thanks in advance!

NAME        SUPERVISOR_ID        REPORTS_TO_ID
Person1     2                                 1
Person2     12                               2
Person3     37                               12
Person4     62                               37
Person5     154                             37
Person6     144                             2
Person7     168                             144
Person8     69                               168

How can I write a query to output the structure so it looks like this:
Person1 = “CEO -0”
   Person6 = “CEO -1”
      Person7 = “CEO -2”
         Person8 = “CEO -3”
   Person2 = “CEO -1”
      Person3 = “CEO -2”
        Person4 = “CEO -3”
      Person5 = “CEO -3”
ASKER CERTIFIED SOLUTION
Mike Eghtebas
Database and Application Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros