• Status: Solved
• Priority: Medium
• Security: Public
• Views: 217

# Return multiple values in a heirarchical data structure within a single table

Hello,

I have a table containing Org Unit details for a company with 6 levels of heirarchy.  Each lower level record is linked to a superior Org Unit from level 6 upwards.  The table columns are structured as follows:

ID (PK) | Reference | Org Unit Level | Org Unit Name | Superior Org Unit Number

For example:
NB: 0 is Organisation Level (i.e. top level)

1  |  123456  |  01  |  OrgUnitName1  |  0
2  |  345678  |  02  |  OrgUnitName2  |  01
3  |  978657  |  03  |  OrgUnitName2  |  02

etc.

At level 6 there are several hundred different Org Unit categories each of which link up the chain until finally getting to level 0.  Another table contains employee details.  Each employee record has a Reference number (as described above) against it.

What I need is a query that takes a given reference number from the Employee table (as an input to the query) and returns the Org Unit Name for both the level 2 and level 3 Org Units.  I.e. The Reference number is essentially a link to the level 6 Org Unit.  I need to move up the tree and return levels 2 and 3.

Hopefully this is possible.  Thanks very much.

0
tim_chamberlain
• 2
1 Solution

How do you know which SuperiorOrgUnitNumber record a given record links to?  Is it the OrgUnitName?
0

Author Commented:
Hopefully this is a clearer example:

Each row has a superior org unit number i.e.

ID |  Org Unit Number | Reference | Org Unit Level  | Org Unit Name  | SuperiorOrgUnit
-------------------------------------------------------------------------------
1  | 1                        |                 |  01                 |  OrgUnitName1  |  0
2  | 26                      |                 |  02                 |  OrgUnitName2  |  1
3  | 43                      |                 |  03                 |  OrgUnitName3  |  26
4  | 67                      |                 |  04                 |  OrgUnitName4  |  43
5  | 12                      |                 |  05                 |  OrgUnitName5  |  67
6  | 56                      | 123456     |  06                 |  OrgUnitName6  |  12

In the above example Reference 123456 is a level 6 Org Unit (Only level 6 Org Units have Reference Numbers as only this level can be linked to an employee).  The OrgUnitNumber is used to link the different levels of individual records.
0

Commented:
First, create a user-defined function that will return the org unit name at a specific level for a given reference:

CREATE FUNCTION dbo.GetOrgUnitName ( @Reference INT, @OrgUnitLevel char(2))
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @OrgUnitName VARCHAR(50)
DECLARE @UnitLevel CHAR(2)
DECLARE @SuperiorOrgUnit INT

SELECT @UnitLevel = OrgUnitLevel, @SuperiorOrgUnit = SuperiorOrgUnit
FROM OrgUnit
WHERE Reference = @Reference

WHILE (@UnitLevel != @OrgUnitLevel AND @UnitLevel IS NOT NULL)
BEGIN

SET @UnitLevel = NULL
SELECT @UnitLevel = OrgUnitLevel, @SuperiorOrgUnit = SuperiorOrgUnit, @OrgUnitName = OrgUnitName
FROM OrgUnit
WHERE OrgUnitNumber = @SuperiorOrgUnit

END

RETURN @OrgUnitName

END

Then you can use this function in your query:

SELECT Reference, dbo.GetOrgUnitName (Reference, '02'), dbo.GetOrgUnitName (Reference, '03')
FROM Employees
0

Author Commented:
Thanks very much rafrancisco,

Great solution and worked perfectly.

Cheers, Tim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.