How to Know parent Region Level name in Region Table?

Question

I create this table

CREATE TABLE Region
(
  RegionID INT PRIMARY KEY ,
  ParentRegionID INT FOREIGN KEY REFERENCES dbo.Region,
  RegionName NVARCHAR(30)
);

and I Insert some values like this :

    INSERT  INTO dbo.Region
        ( RegionID, ParentRegionID, RegionName )
VALUES  ( 1, NULL, N'USA' ),
        ( 2, 1, N'California' ),
        ( 3, 2, N'Los Angeles' ),
        ( 4, 2, N'California City' ),
        ( 5, 2, N'San Diego' ),
        ( 6, 2, N'Bakersfield' ),
        ( 7, 1, N'Alaska' ),
        ( 8, 7, N'Wrangell' ),
        ( 9, 7, N'Anchorage' ),
        ( 10, 7, N'Juneau' );

I would like my query to return

ParentRegionID RegionID RegionName RegionLevelName

---

NULL           1            USA            Country
1              2            California     State
1              7            Alaska         State
7              8            Wrangell       City
7              9            Anchorage      City
7             10            Juneau         City
2              3            Los Angeles    City
2              4            CaliforniaCity City
2              5            San Diego      City
2              6            Bakersfield    City

Answer 1

Try this code

WITH    RegionTree ( ParentRegionID, RegionID, RegionName, RegionLevel )
      AS (
           SELECT   ParentRegionID ,
                    RegionID ,
                    RegionName ,
                    0 AS RegionLevel
           FROM     Region
           WHERE    ParentRegionID IS NULL
           UNION ALL
           SELECT   c.ParentRegionID ,
                    c.RegionID ,
                    c.RegionName ,
                    p.RegionLevel + 1
           FROM     Region c
                    INNER JOIN RegionTree p ON c.ParentRegionID = p.RegionID
         )
SELECT  ParentRegionID ,
        RegionID ,
        RegionName ,
        CASE WHEN RegionLevel = 0 THEN 'Country'
             WHEN RegionLevel = 1 THEN 'State'
             WHEN RegionLevel = 2 THEN 'City'
        END AS RegionLevelName
FROM    RegionTree;