CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008sql-server-2012ssms
Mukund
Mukund

Reputation: 1689

t-sql query to update data recursively

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?

Upvotes: 0

Views: 833

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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!

Upvotes: 1

Related Questions