Use SQL Server CTE to return all Parent Records

Question

I have the following table Class Hierarchy structure.

ClassID    ParentID
--------------------
PMM_00001  null
PMM_00010  PMM_00001
PMM_00110  PMM_00010
PMM_00020  PMM_00001
PMM_00220  PMM_00020

and I want to get the following results.

ClassID    Class_Join
---------------------
PMM_00001  PMM_00001
PMM_00010  PMM_00001
PMM_00010  PMM_00010
PMM_00110  PMM_00001
PMM_00110  PMM_00010
PMM_00110  PMM_00110
PMM_00020  PMM_00001
PMM_00020  PMM_00020
PMM_00220  PMM_00001
PMM_00220  PMM_00020
PMM_00220  PMM_00220

I plan on using these results to get the inherited characteristics by joining to my characteristics table. In the past I have used loops to get the data setup properly, but I would like to use a CTE to get these results.

Here is what I have tried so far.

;WITH ClassHierarchy_CTE (ClassID, ClassID_Join)
AS
(
SELECT
    c.ClassID,
    c.ClassID
FROM
    ClassHierarchy AS h
WHERE
    h.ParentID IS NULL
UNION ALL
SELECT
    c.ClassID,
    h.ParentID
FROM
    ClassHierarchy AS h INNER JOIN ClassHierarchy_CTE
        ON
            h.ParentID = ClassHierarchy_CTE.ClassID
)
SELECT
    *
FROM
    ClassHierarchy_CTE
ORDER BY
    ClassID

The results that I get from this simply walk down the hierarchy to get the complete list. I need to get the reference back to each level in the hierarchy so that I can get the complete list of characteristics. Any help is appreciated! I am open to suggestions if there is a better way to do this.

Answer 1

Looks like I was approaching this all wrong. The concept that I was missing is that I needed to move UP the hierarchy, not DOWN (as most of the documentation/articles out there demonstrate). So here's what I did.

CREATE TABLE hierarchy
(
  ClassID  nvarchar(100), ParentID nvarchar(100)
)

INSERT INTO hierarchy
( ClassID     ,           ParentID)
VALUES
( N'PMM_00001',      NULL    ),
( N'PMM_00010',      N'PMM_00001'),
( N'PMM_00110',      N'PMM_00010'),
( N'PMM_00020',      N'PMM_00001'),
( N'PMM_00220',      N'PMM_00020')

;WITH ClassHierarchy_CTE (ClassID, ClassID_Join, Level)
AS
(
SELECT
    ClassID,
    ClassID AS Join_Class,
    0
FROM
    hierarchy AS c
UNION ALL
SELECT
    cte.ClassID,
    h.ParentID,
    Level + 1
FROM
    hierarchy AS h INNER JOIN ClassHierarchy_CTE as cte
      ON
        h.ClassID = cte.ClassID_Join
)
SELECT
    *
FROM
    ClassHierarchy_CTE
WHERE
    ClassID_Join IS NOT NULL
ORDER BY
    ClassID,
    Level

Returns...

CLASSID CLASSID_JOIN    LEVEL
---------------------------------
PMM_00001   PMM_00001   0
PMM_00010   PMM_00010   0
PMM_00010   PMM_00001   1
PMM_00020   PMM_00020   0
PMM_00020   PMM_00001   1
PMM_00110   PMM_00110   0
PMM_00110   PMM_00010   1
PMM_00110   PMM_00001   2
PMM_00220   PMM_00220   0
PMM_00220   PMM_00020   1
PMM_00220   PMM_00001   2

SQLfiddle for reference.