Sql-server – Help on Self-Referential CTE SQL SERVER

Your query is running an infinite loop because it always finds more records that can be added. That's because the rows you're adding in the second half of the query (after the UNION ALL) are the exact rows that are in the first half of the query. You won't get an infinite loop if you use the columns from the @MappingPartOne table, not CTE, in the SELECT list. You won't get what you want but you won't get an infinite loop.

If I'm understanding you correctly, when a row's Property_Key is NULL, you want to use the same Property_Key value as used by the row's parent - in other words, the nearest ancestor's Property_Key where that value is not NULL. For example: if the row with Functional_Location_Key = 4505 had Property_Key = 9978 instead of NULL, its descendants (Functional_Location_Key 5066, 6121, and 5068) would display Property_Key = 9978.

We need to make several changes to your query to accomplish this:

• First, as previously noted, the second part of the CTE should output the columns from the @MappingPartOne table, not from CTE. This, however, gives you multiple copies of your rows.

• To get a single copy of each of your rows, you need to start the CTE differently. You're initially explicitly providing a list of the rows you want to see in the first part of the CTE. However, then, in the second part, you're bringing in the descendants of each row from the previous iteration. Since you've included rows in the first part that are the descendants of other rows you've included, you get duplicates as you iterate through the rows.

  Instead, you need to specify just the ultimate parent rows - rows that aren't descendants of any other rows. That means, the rows (in the present case, row) where Parent_Key is NULL.

• Now, the next step is to check if a row's Property_Key is NULL, and (if so) to use the same value as its parent did. It doesn't matter if the parent's Property_Key was also NULL, because then it would be using its parent's Property_Key. During each iteration through our data, we have the current row (from @MappingPartOne) and its parent (from CTE). So, instead of simply using the @MappingPartOne.Property_Key, we check if that's NULL. If it's not, we use it - if it is, we use CTE.Property_Key.

So, this gives us the following query:

WITH CTE AS (
    SELECT 
        m.Functional_Location_Key           
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
    FROM #MappingPartOne m
    WHERE m.Parent_Key IS NULL

    UNION ALL 

    SELECT 
        b.Functional_Location_Key           
        ,b.Parent_Key
        ,b.Level
        ,COALESCE(b.Property_Key, t.Property_Key) as Property_Key
    FROM CTE t
    JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT  
    Functional_Location_Key         
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
;

You can see this in action at this SQLFiddle link.

(If you haven't used it before, COALESCE() works like the ISNULL() function. It has two potential advantages: you can provide more than two arguments, in which case it will return the first non-NULL argument, or NULL if all of them are NULL; and, it's part of the ANSI standard, and thus more likely to be portable to other SQL dialects.)

If you change the WHERE clause criteria in the first part of the CTE to pick a specific row by Functional_Location_Key, then you can pull up any given row and its descendants, ignoring the rest of the hierarchy, in case that's needed.

Per your comments, you seem to want to show the Property_Key from the highest level ancestor with a parent (not from roe 1393, but from row 1399) as the default for all its descendants with a NULL Property_Key.

To do this, we need only need to introduce an additional column to our CTE:

WITH CTE AS (
    SELECT 
        m.Functional_Location_Key           
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
        ,NULL as Top_Property_Key
    FROM MappingPartOne m
    WHERE m.Parent_Key IS NULL

    UNION ALL 

    SELECT 
        b.Functional_Location_Key           
        ,b.Parent_Key
        ,b.Level
        ,COALESCE(b.Property_Key, t.Top_Property_Key) as Property_Key
        ,COALESCE(t.Top_Property_Key, b.Property_Key) as Top_Property_Key
    FROM CTE t
    JOIN MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT  
    Functional_Location_Key         
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
;

The new column is Top_Property_Key. Initially, we're looking at the top level parent. You've said we don't want to use that as our default, so we set the value for this one row to NULL. In the second part of the CTE, we want to use the established Top_Property_Key as our default if it's not currently NULL, otherwise we want to use the current parent row's Property_Key. Once we have a row where Top_Property_Key is assigned a non-NULL value, it will retain that value for all its children.

Finally, we change the COALESCE for each row's Property_Key to use our new column, t.Top_Property_Key, instead of t.Property_Key (the immediate parent's property key value).

Here's an updated SQLFiddle link with the new functionality.

NOTE: If you decide you want to use the very top parent's Property_Key as the default for all its children, then set Top_Property_Key to Property_Key instead of NULL in the first part of the CTE:

        ,m.Property_Key as Top_Property_Key