Sql 递归 CTE 附加为行

我有一个图式

CREATE TABLE tbl_Attribute 
(  
    AttributeID INT NOT NULL,  
    Name VARCHAR(100) NOT NULL 
)

INSERT INTO tbl_Attribute (AttributeID, Name) 
VALUES (1, 'Genius'), (2, 'Smart'), (3, 'Pretty'), (4, 'Ugly')

CREATE TABLE tbl_Person 
( 
    PersonID INT NOT NULL,  
    Name VARCHAR(100) NOT NULL,  
    ParentPersonID INT NULL 
)

INSERT INTO tbl_Person (PersonID, Name, ParentPersonID) 
VALUES (1, 'GrandMother', NULL), (2, 'Mother', 1), 
       (3, 'Daughter', 2), (4, 'Son', 2)

CREATE TABLE tbl_PersonAttribute 
(
    PersonID INT NOT NULL,  
    AttributeID INT NOT NULL 
)

INSERT INTO tbl_PersonAttribute (PersonID, AttributeID) 
VALUES 
  (1, 1) -- GrandMother - Genius
, (2, 2) -- Mother - Smart
, (3, 3) -- Daughter - Pretty
, (4, 4) -- Son - Ugly

我正在try 获取显示所有属性(self +继承)的人的行

到目前为止，我有这样一个SQL:

WITH PersonAttrCTE AS 
(
    -- get base (parent is null)
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        CAST('Myself' AS VARCHAR(100)) [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        tbl_PersonAttribute pa ON p.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID
    WHERE 
        p.ParentPersonID IS NULL

    UNION ALL

    -- get the direct attributes
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        pCTE.Name [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        PersonAttrCTE pCTE ON p.ParentPersonID = pCTE.PersonID
    INNER JOIN 
        tbl_PersonAttribute pa ON p.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID

    UNION ALL

    -- get inherited attributes
    SELECT 
        p.PersonID,
        p.Name,
        a.Name AS AttributeName,
        pac.Name [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        PersonAttrCTE pac ON p.ParentPersonID = pac.PersonID
    INNER JOIN 
        tbl_PersonAttribute pa ON pac.PersonID = pa.PersonID
    INNER JOIN 
        tbl_Attribute a ON pa.AttributeID = a.AttributeID
)
SELECT DISTINCT
    *
FROM 
    PersonAttrCTE c
ORDER BY 
    PersonID

不知何故，它在一定程度上起到了作用，因为继承没有达到最高层.

我是这样期待的

-- Mother should have both Smart (Myself) and Genius (GrandMother)
-- Daughter should have Pretty (Myself), Smart (Mother), Genius (GrandMother)
-- Son should have Ugly (Myself), Smart (Mother), Genius (GrandMother)

添加了小提琴就绪http://sqlfiddle.com/#!18/3a25e/1

如有任何帮助，我们将不胜感激