CODEWITHSUNDEEP
sqlsql-serverhierarchy
Singularity222
Singularity222

Reputation: 327

Creating data hierarchy in SQL Server

So I have been tasked with creating a SQL Server stored procedure to assemble the geography hierarchy for an employee.

The system has 3 types of geography:

  1. National Geography (Root)
  2. Regional Geography (Level 1)
  3. Territory Geography (level 2)

In the database the geographies table looks similar to this:

GeographyID | GeographyType | GeographyName | ParentGeographyID |
-----------------------------------------------------------------
1             National        Nation          NULL
2             Region          South           1
3             Territory       Florida         2

There is also and employee table. A single employee can be assigned to any of the geographies above.

For example if employee 105 was assigned to the "South" Region there is an entry in an XREF table like so:

EmployeeID | GeographyID
------------------------
105           2

What I need to do is given an employee ID, build their geography hierarchy. So the result for employee 105 would look something like this:

EmployeeID | TerritoryGeographyID | RegionGeographyID | NationalGeographyID
---------------------------------------------------------------------------
105          NULL                    2                    1

I'm at a loss of how to set up such a data structure. I'm hoping someone may have some insight on this problem.

Upvotes: 2

Views: 689

Answers (1)

Serpiton
Serpiton

Reputation: 3684

Given that the expected result should show all the level, and the level are few there are solution that don't use recursive CTE, for example

WITH G AS (
  SELECT g.GeographyID 
       , t.GeographyID TerritoryGeographyID
       , r.GeographyID RegionGeographyID
       , n.GeographyID NationalGeographyID
  FROM   Geography g
         LEFT JOIN Geography T ON (g.GeographyID = T.GeographyID)
                              AND (T.GeographyType = 'Territory')
         LEFT JOIN Geography R ON ((g.GeographyID = R.GeographyID) 
                                OR (R.GeographyID = T.ParentGeographyID))
                              AND (R.GeographyType = 'Region')
         LEFT JOIN Geography N ON ((g.GeographyID = N.GeographyID) 
                                OR (N.GeographyID = R.ParentGeographyID))
                              AND (N.GeographyType = 'National')
)
SELECT E.EmployeeID
     , TerritoryGeographyID
     , RegionGeographyID
     , NationalGeographyID
FROM   Employee E
       INNER JOIN G ON E.GeographyID = G.GeographyID;

In the CTE the Geography is partitioned and reassembled from vertical to horizontal, the main query JOIN the 'PIVOTed' data to the employee data, the ID can be changed to the location name by changing [trn].GeographyID to [trn].GeographyName

The partitioning can be explicited defining a CTE for every level

With N AS (
  SELECT GeographyID, GeographyName
  FROM   Geography
  WHERE  GeographyType = 'National'
), R AS (
  SELECT GeographyID, GeographyName, ParentGeographyID
  FROM   Geography
  WHERE  GeographyType = 'Region'
), T AS (
  SELECT GeographyID, GeographyName, ParentGeographyID
  FROM   Geography
  WHERE  GeographyType = 'Territory'
), G AS (
  SELECT g.GeographyID 
       , t.GeographyID TerritoryGeographyID
       , r.GeographyID RegionGeographyID
       , n.GeographyID NationalGeographyID
  FROM   Geography g
         LEFT JOIN Geography T ON (g.GeographyID = T.GeographyID)
         LEFT JOIN Geography R ON (g.GeographyID = R.GeographyID)
                               OR (R.GeographyID = T.ParentGeographyID)
         LEFT JOIN Geography N ON (g.GeographyID = N.GeographyID) 
                               OR (N.GeographyID = R.ParentGeographyID)
)
SELECT E.EmployeeID
     , TerritoryGeographyID
     , RegionGeographyID
     , NationalGeographyID
FROM   Employee E
       INNER JOIN G ON E.GeographyID = G.GeographyID;

SQLFiddle demo

Upvotes: 3

Related Questions