Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Hierarchy table to exclude root
DECLARE @t table (ID int not null, Name varchar(19) not null, ParentID int null) insert into @t(ID,Name,ParentID) values (0 ,'users',NULL), (1 ,'Alex',0), (2 ,'John',0), (3 ,'Don',1), (4 ,'Philip',2), (5 ,'Shiva',2), (6 ,'San',3), (7 ,'Antony',6), (8 ,'Mathew',2), (9 ,'Cyril',8), (10,'Johan',9) declare @search table (ID int not null) insert into @search (ID) values (7),(10), (1); ;With Paths as ( select s.ID as RootID,t.ID,t.ParentID,t.Name, CASE WHEN t.ParentId IS NULL THEN '-' ELSE CONVERT(varchar(max),t.Name) END as Path from @search s join @t t on s.ID = t.ID union all select p.RootID,t.ID,t.ParentID,p.Name, t.Name + '->' + p.Path from Paths p join @t t on p.ParentID = t.ID ) select * from Paths where ParentID is null
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Usage of joins
member
Common Table Expression
9.12.2020
search all tables for string
Cross Apply vs Inner Join
Movies
bdLojaRoupas
FIRST DATABASE
Max from multiple columns
Please log in to post a comment.