Avatar of nightshadz
nightshadz
Flag for United States of America asked on

SQL - Ordering Supervisor Hierarchy

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

Open in new window


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”
Microsoft SQL Server

Avatar of undefined
Last Comment
nightshadz

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mike Eghtebas

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
nightshadz

ASKER
This is perfect! I tweaked it a bit and it gives me the results I need. Thank you!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes