Denormalized Hierarchy

Say we have a multiple levels hierarchy that we need to represent in such a way that all the levels are displayed side by side. For example, consider the following Territory hierarchy with 5 levels.

Territory -> Area -> Region -> Zone -> Nation -> Global

And say the source table tblTerritory has the following structure:

Id
Name
ParentId
TerritoryType

We would like to have the result set with the following columns:
TerritoryId, TerritoryName, TerritoryType, AreaName, RegionName, ZoneName, NationName, GlobalName

In this post, I would like to show a couple of ways to achieve this.

Method 1:

First populate the Territory information and then update the parent information one at a time.

DECLARE @Territory TABLE
  (
     territoryid       NVARCHAR(255),
     territoryname     NVARCHAR(255),
     territorytype     NVARCHAR(255),
     parentterritoryid NVARCHAR(255),
     areaname          NVARCHAR(255),
     regionname        NVARCHAR(255),
     zonalname         NVARCHAR(255),
     nationname        NVARCHAR(255),
     globalname        NVARCHAR(255)
  );

INSERT INTO @Territory
            (territoryid,
             territoryname,
             territorytype,
             parentterritoryid)
SELECT NAME,
       territorytype,
       parentterritoryid
FROM   tblterritory 

--Update Area
UPDATE t
SET    areaname = A.NAME
FROM   @Territory T
       JOIN tblterritory A
         ON ( ( T.parentterritoryid = A.id
                 OR T.territoryid = A.id )
              AND A.territorytype = 'Area' ); 

-- Update Region
UPDATE t
SET    regionname = R.NAME
FROM   @Territory T
       JOIN tblterritory A
         ON T.areaname = A.NAME
       JOIN tblterritory R
         ON ( ( COALESCE(A.parentid, T.parentterritoryid) = R.id
                 OR COALESCE(A.id, T.territoryid) = R.id )
              AND R.territorytype = 'Region' ); 

-- Update Zone
UPDATE t
SET    zonename = Z.NAME
FROM   @Territory T
       JOIN tblterritory A
         ON T.areaname = A.NAME
       JOIN tblterritory R
         ON T.regionname = R.NAME
       JOIN tblterritory Z
         ON ( ( COALESCE(R.parentid, A.parentid, T.parentterritoryid) = Z.id
                 OR COALESCE(R.id, A.id, T.territoryid) = Z.id )
              AND Z.territorytype = 'Zone' ); 

-- Update Nation
UPDATE t
SET    nationname = N.NAME
FROM   @Territory T
       JOIN tblterritory A
         ON T.areaname = A.NAME
       JOIN tblterritory R
         ON T.regionname = R.NAME
       JOIN tblterritory Z
         ON T.zonename = Z.NAME
       JOIN tblterritory N
         ON ( ( COALESCE(Z.parentid, R.parentid, A.parentid, T.parentterritoryid
                ) =
                N.id
                 OR COALESCE(Z.id, R.id, A.id, T.territoryid) = N.id )
              AND Z.territorytype = 'Nation' ); 

-- Update Global
UPDATE t
SET    globalname = G.NAME
FROM   @Territory T
       JOIN tblterritory A
         ON T.areaname = A.NAME
       JOIN tblterritory R
         ON T.regionname = R.NAME
       JOIN tblterritory Z
         ON T.zonename = Z.NAME
       JOIN tblterritory N
         ON T.nationname = N.NAME
       JOIN tblterritory G
         ON ( ( COALESCE(N.parentid, Z.parentid, R.parentid, A.parentid,
                T.parentterritoryid
                )
                       =
                         G.id
                 OR COALESCE(N.id, Z.id, R.id, A.id, T.territoryid) = G.id )
              AND Z.territorytype = 'Global' );

SELECT territoryid,
       territoryname,
       territorytype,
       areaname,
       regionname,
       zonalname,
       nationname,
       globalname
FROM   @Territory 

Method 2:

Get everything in a single statement using Case statements.

SELECT T.NAME AS TerritoryName,
       T.territorytype,
       CASE
         WHEN T.territorytype = 'Area' THEN T.NAME
         WHEN A.territorytype = 'Area' THEN A.NAME
       END    AreaName,
       CASE
         WHEN T.territorytype = 'Region' THEN T.NAME
         WHEN A.territorytype = 'Region' THEN A.NAME
         WHEN R.territorytype = 'Region' THEN R.NAME
       END    RegionName,
       CASE
         WHEN T.territorytype = 'Zone' THEN T.NAME
         WHEN A.territorytype = 'Zone' THEN A.NAME
         WHEN R.territorytype = 'Zone' THEN R.NAME
         WHEN Z.territorytype = 'Zone' THEN Z.NAME
       END    ZoneName,
       CASE
         WHEN T.territorytype = 'Nation' THEN T.NAME
         WHEN A.territorytype = 'Nation' THEN A.NAME
         WHEN R.territorytype = 'Nation' THEN R.NAME
         WHEN Z.territorytype = 'Nation' THEN Z.NAME
         WHEN N.territorytype = 'Nation' THEN N.NAME
       END    NationName,
       CASE
         WHEN T.territorytype = 'Global' THEN T.NAME
         WHEN A.territorytype = 'Global' THEN A.NAME
         WHEN R.territorytype = 'Global' THEN R.NAME
         WHEN Z.territorytype = 'Global' THEN Z.NAME
         WHEN N.territorytype = 'Global' THEN N.NAME
         WHEN G.territorytype = 'Global' THEN G.NAME
       END    GlobalName
FROM   tblterritory T
       LEFT JOIN tblterritory A
              ON T.parentid = A.id
       LEFT JOIN tblterritory R
              ON A.parentid = R.id
       LEFT JOIN tblterritory Z
              ON R.parentid = Z.id
       LEFT JOIN tblterritory N
              ON Z.parentid = N.id
       LEFT JOIN tblterritory G
              ON N.parentid = G.id --WHERE   T.TerritoryType = 'Territory';

I prefer the second method as it encapsulates everything we want neatly into a single statement.