Recursive CTE append as rows

Question

I have this schema

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

I'm trying to get rows for a person that shows all attributes (self + inherited)

I have this SQL so far:

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

It's somehow partially working because inheritance didn't reached up to the top most level.

I'm expecting like this

-- 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)

Added fiddle ready http://sqlfiddle.com/#!18/3a25e/1

Any help would be appreciated

Answer 1

I believe this gets the result you were looking for:

WITH PersonAttrCTE AS 
(
    -- get all root values
    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

    UNION ALL

    -- get inherited attributes from all levels
    SELECT 
        p.PersonID,
        p.Name,
        pac.AttributeName,
        CASE WHEN pac.Inherit = 'Myself' THEN pac.Name ELSE pac.Inherit END [Inherit]
    FROM 
        tbl_Person p
    INNER JOIN 
        PersonAttrCTE pac ON p.ParentPersonID = pac.PersonID
)
SELECT
    *
FROM 
    PersonAttrCTE c
ORDER BY 
    PersonID

Output:

PersonID	Name	AttributeName	Inherit
1	GrandMother	Genius	Myself
2	Mother	Smart	Myself
2	Mother	Genius	GrandMother
3	Daughter	Genius	GrandMother
3	Daughter	Smart	Mother
3	Daughter	Pretty	Myself
4	Son	Ugly	Myself
4	Son	Smart	Mother
4	Son	Genius	GrandMother