Basic CTE query, get full path of something recursive

Article
11/24/2010

Question

_{Wednesday, November 24, 2010 7:22 PM}

Hello,

I'm trying to create a CTE, to get the full path of something recursive, but I'm not really sure how to do so.

The table looks like this:

CREATE TABLE Category (
  Cat_ID bigint identity(1,1) NOT NULL,
  Cat_ParentID bigint NOT NULL,
  Cat_Name varchar(50) NOT NULL
);

When Cat_ID = Cat_ParentID its a root node, and does not have any parent.

This is what I have so far:

WITH FullNameCTE
AS
(
 SELECT Cat_ID,
     Cat_ParentID,
     Cat_Name,
     0 AS chain_level
 FROM dbo.Category
 UNION ALL
 SELECT
     b.Cat_ID,
     b.Cat_ParentID,
     a.Cat_Name,
     a.Cat_Name + b.Cat_Name as FullName,
     a.chain_level + 1
 FROM FullNameCTE AS a
  JOIN dbo.Category AS b
   ON a.Cat_ParentID = b.Cat_ID
   WHERE a.Cat_ParentID != a.Cat_ID

)
 SELECT * FROM FullNameCTE

And I've tried different variations of that.

So lets say I'd insert this:

INSERT INTO dbo.Category(Cat_ID,Cat_ParentID, Cat_Name)
VALUES(1,1,'I')
,(2,1,'Like')
,(3,2,'Fishing')

What I'd like to have returned is:

(Cat_ID,Cat_ParentID, Cat_Name, FullName)
(1, 1, I, I)
(2, 1, Like, I Like)
(3, 2, Fishing, I Like Fishing)

I've tried lots of things already, but I never really used a CTE before, so I'm not really sure what its suppose to look like.

Any help is really appreciated.

Thanks

Edit: the SQL Code Blocks seems to cripple my post, so I removed them...

All replies (3)

_{Wednesday, November 24, 2010 7:44 PM ✅Answered | 1 vote}

Check this

 -- Anchor
 ;with cte as (select C.Cat_ID, C.Cat_ParentID, cast(C.Cat_Name as varchar(max)) as FullName, 0 as Rec_Level from 
 Category C where C.Cat_ID = C.Cat_ParentID 
 -- Recursive
 UNION ALL
 select c1.Cat_ID, C1.Cat_ParentID, C1.Cat_Name + ' ' + C.FullName as FullName, C.Rec_Level + 1 as Rec_Level 
 from Category C1 inner join cte C on c1.Cat_ParentID = c.Cat_ID and c1.Cat_ID <> c1.Cat_ParentID )
 
 select * from cte

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Wednesday, November 24, 2010 7:52 PM}

THANK YOU!

Its actually

...
 select c1.Cat_ID, C1.Cat_ParentID, C.FullName + ' ' + C1.Cat_Name as FullName, C.Rec_Level + 1 as Rec_Level 
...

else you get the results in reverse ("Fishing Like I"), but you probably came up with the query without running it, so pretty impressive :)

Solved my problems

_{Wednesday, November 24, 2010 8:11 PM | 1 vote}

I ran it, but in my case I needed the reverse path (see my sample). Took me about 3 or 4 tries before I got it right.

CREATE
 TABLE
 Category ( 

    Cat_ID bigint
 identity
(1,1) NOT
 NULL
,
    Cat_ParentID bigint not null
 
 
,
    Cat_Name varchar
(50) NOT
 NULL)
 
 insert into Category values (1, 'Vehicles')
 insert into Category values (1, 'Bikes')
 insert into Category values (1, 'Cars')
 insert into Category values (2, 'Road Bikes')
 insert into Category values (3, 'Honda')
 
 -- Anchor
 ;with cte as (select C.Cat_ID, C.Cat_ParentID, cast(C.Cat_Name as varchar(max)) as FullName, 0 as Rec_Level from 
 Category C where C.Cat_ID = C.Cat_ParentID 
 -- Recursive
 UNION ALL
 select c1.Cat_ID, C1.Cat_ParentID, C1.Cat_Name + ' ' + C.FullName as FullName, C.Rec_Level + 1 as Rec_Level 
 from Category C1 inner join cte C on c1.Cat_ParentID = c.Cat_ID and c1.Cat_ID <> c1.Cat_ParentID )
 
 select * from cte