Hierarchy in SQL

We have a sql database at work with a table, employees that has a column, report_to, which contains the username of the person that that employee reports to. What we want t开发者_JS百科o do is change this representation to a numerical representation. For instance:

---

'a' reports to 'b' reports to 'c'. So the representation would be something like 'a' = 49, 'b' = 50, 'c' = 51. if 'd' becomes 'c''s boss, then 'd' = 52. If 'a' becomes the supervisor of interns 'e' and 'f', then 'e' and 'f' both are equal to 48.

---

As shown, starting the numbers at a non zero number allows for expansion not only upwards but also down the hierarchical chain.

The main question is, how do I convert from the current structure ("report_to"), to a numerical representation?

NOTE: this is in MSSQL

---

You can add a new column (rank) that should be 0.

Then the first step is to find the BIG BOSS - this should be the user who doesn't have a boss - report_to is null. His rank will be 1.

The second step is to find his first directs. They will rank as 2. Something like:

UPDATE TABLE SET RANK = 2 
WHERE report_to IN 
 (SELECT username FROM TABLE WHERE RANK = 1)

The third step is to find directs's directs. Something like:

UPDATE TABLE SET RANK = 3 
WHERE report_to IN 
 (SELECT username FROM TABLE WHERE RANK = 2)

The next steps are identical with step 2 and 3, until no RANK = 0 is found.

All these steps can be done in a procedure, within a WHILE statement.

In the end, if you would like to start the ranking from 50 instead on 1, then you can make an update:

UPDATE TABLE SET RANK = 50 - RANK

or to be sure you don't miss anything:

UPDATE TABLE SET RANK = (SELECT MAX(RANK) FROM TABLE) + 1 - RANK

---

If you have a field that contains the supervisor of the employee in the table, you can use a recusive CTE to get the hierarchy. Looks that up in Books ONline and get back to us if you have any qquestions.

---

wow... so do you have a users table? if not, then suggestion 1 is to create one.

users_table
------------
username
user_id
name_first
name_last
other_stuff_?

then populate that with all the existing usernames - possibly by querying the table you are describing for unique names. the user_id will be populated as a sequenced id value during this step.

then you can add a new table,

user_user
-----------
user_id_1
user_id_2
relationship
begin_dt
end_dt

then you can populate this new table with each user to user relationship and when it was valid. e.g. user 48 was related to user 50 beginning on someday with relationship = 'Manages'

the relationship should be probably a fk to yet another table... but i leave that to you as an excercise.

---

In my opinion, you don't need to use numeric counters, just use positions, because you can't have unlimited position, its gonna stop somewhere. Each username should have a position, like intern, supervisor, employer, project manager or whatever. When you change lets say interns position higher then supervisor's it becomes employer, or something similar. You get the idea. :)