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!

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”
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