• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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
Asked:
tim_chamberlain
  • 2
1 Solution
 
Brian CroweCommented:
How do you know which SuperiorOrgUnitNumber record a given record links to?  Is it the OrgUnitName?
0
 
tim_chamberlainAuthor 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
 
rafranciscoCommented:
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
 
tim_chamberlainAuthor Commented:
Thanks very much rafrancisco,

Great solution and worked perfectly.

Cheers, Tim.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now