t-sql query to update data recursively

Question

Max_ID     Second_Max_ID   Cumulative_ID
173          97             ,97
174          173            ,97,173
...          ...            ...

I need to write a query to compute the cumulative id. Can someone help? It's a temporary table with 3 columns(Max_ID,Second_Max_ID,Cumulative_ID) where I have just Max_ID and Second_Max_ID value in table and I need to compute Cumulative_ID for each row in the same temporary table.

I used query provided by shnugo and modified the query as below -

DECLARE @mockup TABLE(Max_ID INT,Second_Max_ID INT);
INSERT INTO @mockup VALUES
(173 , 97   )
,(174 , 173  );

WITH recCTE AS
(
    SELECT Max_ID
          ,Second_Max_ID
          ,CAST(Second_Max_ID AS VARCHAR(MAX)) AS Cumulative_ID
    FROM @mockup --WHERE Second_Max_ID IS NULL

    UNION ALL
    SELECT m.Max_ID
          ,m.Second_Max_ID
          ,r.Cumulative_ID+','+ cast(m.Second_Max_ID as varchar(max))
    FROM @mockup m 
    INNER JOIN recCTE r ON r.Max_ID=m.Second_Max_ID
)
SELECT * FROM recCTE;

Now getting result like -

Max_ID  Second_Max_ID   Cumulative_ID
173          97            97
174          173          173
174          173          97,173

How can I remove the second row from above result?

Not enough for me to close as a 100% duplicate, but you'll still need [this methodology](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) or something similar. You'll need to change the join clause up a bit., assuming you have a primary key that's a iterated value — S3S, Jan 30 '19 at 17:20
Is there always just one follower below one `Max_ID`? Is it a `1:1` list, a `1:n` or even a `m:n` hierarchy? — Shnugo, Jan 30 '19 at 17:25
@Shnugo it's a sequence of action you can say where on any record first action happened and for that ID was 97 then second action happened and for that ID is 173 then 3rd and so on. Now I need to show the the audit like 97>173>174 — Mukund, Jan 30 '19 at 17:28

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You can try this:

DECLARE @mockup TABLE(Max_ID INT,Second_Max_ID INT);
INSERT INTO @mockup VALUES
 (97   ,NULL  )
,(173 , 97   )
,(174 , 173  )
,(175 , 174  );

WITH recCTE AS
(
    SELECT Max_ID
          ,Second_Max_ID
          ,CAST(Max_ID AS VARCHAR(MAX)) AS Cumulative_ID
    FROM @mockup WHERE Second_Max_ID IS NULL

    UNION ALL
    SELECT m.Max_ID
          ,m.Second_Max_ID
          ,CONCAT(r.Cumulative_ID,',',m.Max_ID)
    FROM @mockup m 
    INNER JOIN recCTE r ON r.Max_ID=m.Second_Max_ID
)
SELECT * FROM recCTE;

The idea is a recursive CTE (rather an iterative approach). You start of with the one row with no parent (the Second_Max_ID IS NULL).

This traverses down the list (a hidden RBAR) and does the concatenation on the fly.

You've tagged with v2008 and v2012. If CONCAT() does not work for you, you can easily use simple + with the needed convert to varchar.

UPDATE

In this version I'll add a counter for the hierarchy depth:

WITH recCTE AS
(
    SELECT Max_ID
          ,Second_Max_ID
          ,CAST(Max_ID AS VARCHAR(MAX)) AS Cumulative_ID
          ,1 AS HierarchyLevel
    FROM @mockup WHERE Second_Max_ID IS NULL

    UNION ALL
    SELECT m.Max_ID
          ,m.Second_Max_ID
          ,CONCAT(r.Cumulative_ID,',',m.Max_ID)
          ,r.HierarchyLevel+1
    FROM @mockup m 
    INNER JOIN recCTE r ON r.Max_ID=m.Second_Max_ID
)
SELECT * 
FROM recCTE;

You can use

a WHERE to filter for a special level
TOP 1 in connection with ORDER BY HierachyLevel DESC to get the last element (the one with the longest path).

Hope this helps!

@Mukund May I ask why? There are (rather ugly) approaches (e.g. a `CURSOR` loop), but this seems to be an efficient, inlinable(!) approach... — Shnugo, Jan 30 '19 at 17:37
@Mukund CTEs are pretty much the best way to do recursion in SQL Server. — digital.aaron, Jan 30 '19 at 18:40

t-sql query to update data recursively

1 Answers1

UPDATE