Recursive CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive CTE

Author

Topic

Neilson
Starting Member

11 Posts

Posted - 2011-03-18 : 10:28:48

I have a table with navigation that joins back on its self using ParentId. I am trying to calculate how many descendent's each record has, I know that I need to increment a counter in the recursion, I'm just not sure how to go about it, and with my example below, I get "Invalid column name 'Level'" where I increment Level + 1!

Any help would be greatly appreciated!


CREATE TABLE [dbo].[Navigation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AnchorText] [varchar](50) NOT NULL,
    [ParentID] [int] NULL)

insert into Navigation
select 'Level 1', null
union
select 'Level 2', 1
union
select 'Level 3', 2
union
select 'Level 4', 3


WITH NavigationCTE (Id, AnchorText, ParentId, Level) as
(
      Select nav.Id, nav.AnchorText, nav.ParentId, 0 as Level
      from dbo.Navigation AS nav

      UNION ALL 

      select nav.Id, nav.AnchorText, nav.ParentId, Level + 1
      from dbo.Navigation AS nav

      join Navigation AS nav2 ON nav.ParentId = nav2.Id
)


SELECT * FROM NavigationCTE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 10:34:13

[code]
WITH NavigationCTE (Id, AnchorText, ParentID, [Level]) as
(
Select nav.Id, nav.AnchorText, nav.ParentID, 0 as [Level]
from dbo.Navigation AS nav
where ParentID is null

UNION ALL

select nav.Id, nav.AnchorText, nav.ParentID, nav2.[Level] + 1
from dbo.Navigation AS nav
join NavigationCTE AS nav2 ON nav.ParentID = nav2.Id
)
SELECT * FROM NavigationCTE
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Neilson
Starting Member

11 Posts

Posted - 2011-03-18 : 10:40:15

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-18 : 10:41:48

Works for me

DECLARE	@Navigation TABLE
	(
		ID INT NOT NULL,
		AnchorText VARCHAR(50) NOT NULL,
		ParentID INT NULL
	)

INSERT	@Navigation
SELECT	45, 'Level 1', NULL UNION ALL
SELECT	19, 'Level 2', 45 UNION ALL
SELECT	24, 'Level 3', 19 UNION ALL
SELECT	-8, 'Level 4', 24

;WITH cteNavigation(ID, AnchorText, ParentID, Lvl)
AS (
	SELECT	ID,
		AnchorText,
		ParentID,
		0 AS Lvl
	FROM	@Navigation
	WHERE	ParentID IS NULL

	UNION ALL

	SELECT		n.ID,
			n.AnchorText,
			n.ParentID,
			c.Lvl + 1
	FROM		cteNavigation AS c
	INNER JOIN	@Navigation AS n ON n.ParentID = c.ID
)
SELECT	*
FROM	cteNavigation

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources