CODEWITHSUNDEEP
sqlsql-servert-sql
SamSamet
SamSamet

Reputation: 415

Sql Hierarchy loop query

This table represents a category hierarchy and the element at top of the hierarchy has the parent id as NULL. The table is as the exaple below:

**categoryId categoryName parentId**

1           Home         NULL
.           .            .
.           .            .
20          Vehicles      1
.           .           .
35          SUV          20
36          Motorbikes   20
.            .           .
90          BMW          35
91          Toyota       35
.            .           .
234         LandCruiser  91 

Home>Vehicles>SUV>Toyota>LandCruiser

What I am trying to do is to bulild up a sql query which will return me back:

[categoryId],[categoryName] chain of any given [categoryId]. It should loop and get each row until it reaches the row with the parentId==NULL.

As the sample example above 234->91->35->20->1->NULL(STOP)

Upvotes: 1

Views: 1910

Answers (1)

roman
roman

Reputation: 117540

you can use recursive cte:

with cte as (
   select
      t.categoryId, t.categoryName, t.parentId,
      cast(t.categoryId as nvarchar(max)) as path
   from categories as t
   where t.categoryId = 234

   union all

   select
      c.categoryId, c.categoryName, t.parentId,
      c.path + '->' + cast(t.categoryId as nvarchar(max))
   from categories as t
       inner join cte as c on c.parentId = t.categoryId
)
select categoryid, categoryname, path + '->NULL'
from cte
where parentid is null

sql fiddle demo

Upvotes: 3

Related Questions