CODEWITHSUNDEEP
sqlt-sqlsql-server-2012hierarchical-datasql-function
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

Listing all parents hierarchy in delimited string excluding the top most node

Im having a trouble with an already answered question in stackoverflow itself (Question before). So Im just repeating the question with some changes and a trouble in it because of a root element

I have an SQL table like this

ID        Name        ParentID
------------------------------
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
-------------------------

Am looking for an out put like this

if I pass the ID 7,10,1

The out put table will be

ID          Name           Relation
------------------------------------
7           Antony         Alex->Don->San->Antony
10          Johan          John->Mathew->Cyril->Johan
1           Alex           -

From the above answer what I was trying to emphasis is it should not consider the top most node Users whose ID is 0 and parentid is null. So for ID 1, it returned just an empty string for relation or just hyphen (-) How can I achieve that using CTE

Upvotes: 1

Views: 625

Answers (2)

Rodrick Chapman
Rodrick Chapman

Reputation: 5543

Based on your schema and data, this query:

with P as
(
    select *, cast(null as nvarchar) as chain from People where parentID is null
        union all 
            select C.*, cast(coalesce(P.chain, P.name) + '->' + C.name as nvarchar) 
                from People C inner join P on C.parentID = P.id

)

select id, name, coalesce(chain, '-') relation from P where id in (7, 10, 1)

Yields:

id    name    relation
----- ------  ------------------------------
1     Alex    -
10    Johan   John->Mathew->Cyril->Johan
7     Antony  Alex->Don->San->Antony

Rextester Demo.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175944

Based on prev answer:

DECLARE @t table (ID int not null, Name varchar(19) not null, ParentID int null)
insert into @t(ID,Name,ParentID) values
(1 ,'Alex',null),
(2 ,'John',null),
(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;

Rextester Demo

Upvotes: 1

Related Questions