Get parent of parent of Parent from self join table

Pleae copy and run following script.

DECLARE @Locations TABLE
(
    LocationId INT,
    LocationName VARCHAR(50),
    ParentId INT
)

INSERT INTO @Locations SELECT 1, 'Europe', NULL
INSERT INTO @Locations SELECT 2, 'UK', 1
INSERT INTO @Locations SELECT 3, 'England', 2
INSERT INTO @Locations SELECT 4, 'Scotland', 2
INSERT INTO @Locations SELECT 5, 'Wales'开发者_运维问答, 2
INSERT INTO @Locations SELECT 6, 'Cambridgeshire', 3
INSERT INTO @Locations SELECT 7, 'Cambridge', 6
INSERT INTO @Locations SELECT 8, 'North Scotland', 4
INSERT INTO @Locations SELECT 9, 'Inverness', 8
INSERT INTO @Locations SELECT 10, 'Somerset', 3
INSERT INTO @Locations SELECT 11, 'Bath', 10
INSERT INTO @Locations SELECT 12, 'Poland', 1
INSERT INTO @Locations SELECT 13, 'Warsaw', 12

I need following kind of result

Thanks.

There's no way you can do this with the current set of data; how would you know that in the case of LocationId=11, you have a county/country/continent, while in the case of LocationId=13, there's no county - just a country/continent??

And how do you know to "skip" the entries for Somerset, North Scotland etc. from your output result??

You definitely need more information here....

With this recursive CTE (Common Table Expression) query, you can get the "ladder" up the hierarchy to the top, for any given location:

DECLARE @LocID INT = 13

;WITH LocationHierarchy AS
(
    SELECT LocationId, LocationName, ParentId, 1 AS 'Level'
    FROM @Locations
    WHERE LocationId = @LocID

    UNION ALL

    SELECT l.LocationId, l.LocationName, l.ParentId, lh.Level + 1 AS 'Level'
    FROM @Locations l
    INNER JOIN LocationHierarchy lh ON lh.ParentId = l.LocationId
)
SELECT
    LocationName,
    LocationId,
    Level
FROM LocationHierarchy

This CTE works on SQL Server 2005 and up - on SQL Server 2000, you're out of luck, unfortunately (time to upgrade!!).

This again allows you to walk up the hierarchy for a single entry - but it cannot possibly return that data set you're looking for - there's not enough information to determine this from the current data.

For @LocID=13 (Warsaw), you get this output:

LocationName    LocationId  Level
  Warsaw               13             1
  Poland               12             2
  Europe                1             3

and for @LocID=7 (Cambridge), you get:

LocationName    LocationId  Level
  Cambridge             7             1
  Cambridgeshire        6             2
  England               3             3
  UK                    2             4
  Europe                1             5

From there on, you'd have to use some smarts in your app to get the exact output you're looking for.