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

Posted on 2005-04-18
Last Modified: 2010-03-19

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


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.

Question by:tim_chamberlain
    LVL 34

    Expert Comment

    by:Brian Crowe
    How do you know which SuperiorOrgUnitNumber record a given record links to?  Is it the OrgUnitName?

    Author Comment

    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.
    LVL 28

    Accepted Solution

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

    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)

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


    RETURN @OrgUnitName


    Then you can use this function in your query:

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

    Author Comment

    Thanks very much rafrancisco,

    Great solution and worked perfectly.

    Cheers, Tim.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now