Count of Nodes Visited in Recursive CTE

Question

Post reply

Count of Nodes Visited in Recursive CTE

Ken Parchinski

SSChasing Mays

Points: 632
More actions
October 22, 2007 at 2:10 pm

#179520

The following CTE works great; however, I would like to know if it is possible to count or tally the number of nodes visited at each recursive level?
WITH
ProductLineage (ParentNodeID, ParentClassID, ChildNodeID, ChildClassID, Level)
AS
(
SELECT
PR.PARENTPRODNODEID,
PC2.PRODCLASSID,
PR.CHILDPRODNODEID,
PC.PRODCLASSID,
0
FROM
CORE.DBO.PRODUCTRELATIONSHIPS AS PR
JOIN
CORE.DBO.PRODUCTOBJECTS AS PO
ON
PR.CHILDPRODNODEID = PO.PRODNODEID
JOIN
CORE.DBO.PRODUCTCLASSES AS PC
ON
PO.PRODCLASSID = PC.PRODCLASSID
JOIN
CORE.DBO.PRODUCTOBJECTS AS PO2
ON
PO2.PRODNODEID = PR.PARENTPRODNODEID
JOIN
CORE.DBO.PRODUCTCLASSES AS PC2
ON
PO2.PRODCLASSID = PC2.PRODCLASSID
WHERE
PR.PARENTPRODNODEID = @ProductNodeID
UNION ALL
SELECT
PR.PARENTPRODNODEID,
PC2.PRODCLASSID,
PR.CHILDPRODNODEID,
PC.PRODCLASSID,
PL.LEVEL + 1
FROM
CORE.DBO.PRODUCTRELATIONSHIPS AS PR
JOIN
CORE.DBO.PRODUCTOBJECTS AS PO
ON
PR.CHILDPRODNODEID = PO.PRODNODEID
JOIN
CORE.DBO.PRODUCTCLASSES AS PC
ON
PO.PRODCLASSID = PC.PRODCLASSID
JOIN
CORE.DBO.PRODUCTOBJECTS AS PO2
ON
PO2.PRODNODEID = PR.PARENTPRODNODEID
JOIN
CORE.DBO.PRODUCTCLASSES AS PC2
ON
PO2.PRODCLASSID = PC2.PRODCLASSID
INNER JOIN
ProductLineage PL
ON
PL.CHILDNODEID = PR.PARENTPRODNODEID
)
SELECT
ParentNodeID,
ParentClassID,
ChildNodeID,
ChildClassID,
Level
FROM
ProductLineage

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Change the anchor part to GROUP BY and add a COUNT for top level items.

Add a +1 in the recursive part.

N 56°04'39.16"
E 12°55'05.25"

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 2

Thank you. Is it possible to include it as a column in the table returned or does it have to be returned on its own?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Yes you can...

just add the column in the SELECT statement of the WITH clause..

WITH myCTE

AS

(

.....

)

SELECT RecursiveLevel FROM myCTE

--Ramesh

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 4

Thanks Ramesh. This part I am Ok with. It is the SELECT in the anchor recursive sections that I am having trouble with.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

DECLARE@Sample TABLE (ID INT, ParentID INT, Name VARCHAR(100), Amount MONEY)

INSERT@Sample

SELECT7, NULL, 'Ljunggren', 1000 UNION ALL

SELECT3, 1, 'Gulli', 200 UNION ALL

SELECT8, 7, 'Kerstin', 45 UNION ALL

SELECT1, NULL, 'Rosberg', 1 UNION ALL

SELECT4, 2, 'Peter', 222 UNION ALL

SELECT5, 3, 'Susanne', 111 UNION ALL

SELECT2, 1, 'Jan-Eric', 59 UNION ALL

SELECT10, 9, 'Jennie', 2000 UNION ALL

SELECT6, 3, 'Annelie', 666 UNION ALL

SELECT9, 7, 'Kenneth', 100 UNION ALL

SELECT11, 9, 'Jessica', 199

;WITH Yak (Level, ID, ParentID, Name, Amount)

AS (

SELECT0,

s1.ID,

s1.ParentID,

s1.Name,

s1.Amount

FROM@Sample AS s1

LEFT JOIN@Sample AS s2 ON s2.ParentID = s1.ID

WHEREs2.ID IS NULL

UNION ALL

SELECTy.Level + 1,

s.ID,

s.ParentID,

s.Name,

s.Amount + y.Amount

FROMYak AS y

INNER JOIN@Sample AS s ON s.ID = y.ParentID

)

SELECTID,

Name,

ParentID,

Level,

COUNT(*) OVER (PARTITION BY Level) AS RecordsAffected

FROMYak

N 56°04'39.16"
E 12°55'05.25"

Ken Parchinski SSChasing Mays Points: 632 More actions · Answer 6

Ken Parchinski

SSChasing Mays

Points: 632

October 23, 2007 at 9:19 am

#744594

That is awesome! Thanks.