How to loop, search and concatenate string in with cursor in TSQL?

Question

I have this set of data

psrid   parent_psrid    description
1   NULL    Work Management
2   1   Asset Maintenance
3   2   Code
4   3   asset type code
5   3   asset class code
6   3   asset class code - common rate type code
7   3   asset type code - asset status code
8   3   asset material class code - common rate type code
9   3   external interface code
10  2   Asset
11  10  asset
12  10  asset attachment
13  10  asset - common rate type code
14  2   Warranty
15  14  asset warranty attachment
16  1   CRM
17  16  Request
18  17  request
19  16  Code
20  19  crm request type code - crm task type code
21  19  task category code
22  19  request category code
23  19  request type code
24  19  task type code

I want to create a stored procedure to concatenate description of selected psrid with descriptions of all other rows that have parent_psrid which matches the psrid from previous row, so on and so forth until the parent psrid is null. Please note, it will only look and concatenate descriptions with the rows above it (psrid is primary key and sorted).

This is like a zic-zac mapping. For example, if we start at psrid 10 and parent_psrid is 2 as below picture, then the final concatenated string would be Work Management > Asset Maintenance > Asset

I have got to this far but I don't know what to do next.

-- Declare a variables
DECLARE @in_psrid int = 10,
        @psrid nvarchar(255),
        @spsone_datasecurity_model_parent_psrid nvarchar(255),
        @object_description nvarchar(255)

-- Declare a variable of type TABLE. It will be used as a temporary table.
DECLARE @myTable TABLE (
    [psrid]        int,
    [spsone_datasecurity_model_parent_psrid]   int,
    [object_description]   nvarchar(255)
)

-- Insert required data in the variable of type TABLE
INSERT INTO @myTable
SELECT [psrid], [spsone_datasecurity_model_parent_psrid], [object_description]
FROM [IcjisTest].[dbo].[Book2]

-- Declare cursor
DECLARE cur_psrid CURSOR
    FOR SELECT [psrid], [spsone_datasecurity_model_parent_psrid], [object_description]
    FROM @myTable
    WHERE [psrid] = @in_psrid;

OPEN cur_psrid;

FETCH NEXT FROM cur_psrid INTO 
    @psrid, 
    @spsone_datasecurity_model_parent_psrid,
    @object_description;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @psrid + @spsone_datasecurity_model_parent_psrid + @object_description;
        FETCH NEXT FROM cur_psrid INTO 
            @psrid, 
            @spsone_datasecurity_model_parent_psrid,
            @object_description;
    END;

    CLOSE cur_psrid;

DEALLOCATE cur_psrid;

Please help! Thank you.

Answer 1

Any time you think "I should loop" in a database you should immediately think "but there must be a better "set" approach to solving this".

A recursive CTE is the better "set" approach to solving this. Check out Example D in that link in the "Examples" section of the page.

WITH buildstring As
(
    /*
     * Recursive Seed - Starting point of recursive lookup
     */
    SELECT psrid, parent_psrid, description, 1 as depth
    FROM yourtable
    WHERE psrid = 10

    UNION ALL

    /* 
     * Recursive Member - The part that loops until the join(s) fail
     * Note that we refer to the CTE itself (buildstring) in the 
     * FROM clause, which is what causes the recursiveness. 
     */
    SELECT
        yourtable.psrid,
        yourtable.parent_psrid,
        buildstring.description + " " + yourtable.description,
        buildstring.depth + 1
    FROM buildstring
        INNER JOIN yourtable 
            ON buildstring.parent_psrid = yourtable.psrid
    WHERE depth < 20 --Just in case you get into an infinite cycle
          
)
SELECT * 
FROM (SELECT description, row_number() OVER (ORDER BY depth DESC) as rn FROM buildstring) depth
WHERE rn = 1 --Get the last record created by the recursive CTE

That should get you in the ballpark. You'll most likely want to tweak that a bit to get whatever output you are looking for, but that should spit out a description with all parent descriptions of psrid of 10.