CODEWITHSUNDEEP
sqlsql-serverhierarchyid
ogun
ogun

Reputation: 1334

Hierarchyid Problem

I have a table with hierarchyid column. It is like:

[NAME] [PATH]
Ahmet /
Aliye /1/
Selen /1/1/
Erdem /2/
Bilge /2/1/
Aydin /2/2/
Tomrs /2/2/2/

I want to see NAMES like:

[NAMES_WITH_HIERARCHY]
Ahmet
Ahmet/Aliye
Ahmet/Aliye/Selen
Ahmet/Erdem
Ahmet/Erdem/Bilge
Ahmet/Erdem/Aydin
Ahmet/Erdem/Aydin/Tomrs

How can i do this?

Upvotes: 2

Views: 786

Answers (3)

AliReza
AliReza

Reputation: 184

You don't have to use recursive CTE (Denis Valeev)'s Answer, it has low performance. Just join it with herself

DECLARE @hierarchy TABLE (name VARCHAR(20), [path] HIERARCHYID)
INSERT INTO @hierarchy (name, path)
VALUES  
 ('Ahmet', '/')
,('Aliye', '/1/')
,('Selen', '/1/1/')
,('Erdem', '/2/')
,('Bilge', '/2/1/')
,('Aydin', '/2/2/')
,('Tomrs', '/2/2/2/')

--SELECT * FROM @hierarchy AS h

SELECT
    path.GetLevel() As Level,
    (
        SELECT STRING_AGG(A.name, '/')
        FROM
            @hierarchy A
        WHERE
            B.path.IsDescendantOf(A.path) = 1               
    ) AS FullName,
    path.ToString() AS Path
FROM
    @hierarchy B

Output :

Level  Fullname                  Path
------ ------------------------- ----------
0      Ahmet                     /
1      Ahmet/Aliye               /1/
2      Ahmet/Aliye/Selen         /1/1/
1      Ahmet/Erdem               /2/
2      Ahmet/Erdem/Bilge         /2/1/
2      Ahmet/Erdem/Aydin         /2/2/
3      Ahmet/Erdem/Aydin/Tomrs   /2/2/2/

Upvotes: 0

Matt
Matt

Reputation: 46

This answer helped me out too. Thought I would add an improvement it found. Changing

join @hierarchy as h2 on h2.[path].IsDescendantOf(t.[path]) = 1 and 
                         t.[path] <> h2.[path] and 
                         h2.[path].GetLevel() - t.[level] < 2

to 

JOIN @hierarchy AS h2 ON h2.[path].GetAncestor(1) = t.[path]

increased performance from 3 min to 2 sec, this put it on par with self-referencing regardless of indexing.

Upvotes: 3

Denis Valeev
Denis Valeev

Reputation: 6015

Here you go:

declare @hierarchy table (name varchar(20), [path] hierarchyid)
insert into @hierarchy ( name, path )
values  
 ('Ahmet', '/')
,('Aliye', '/1/')
,('Selen', '/1/1/')
,('Erdem', '/2/')
,('Bilge', '/2/1/')
,('Aydin', '/2/2/')
,('Tomrs', '/2/2/2/')

--select * from @hierarchy as h

;with Tree([level], [FullName], [path]) as (
    select h.[path].GetLevel() as [level], cast(h.[name] as varchar(max)), h.[path]
    from @hierarchy as h
    where [path] = '/'
    union all
    select h2.[path].GetLevel(), t.[FullName] + '/' + h2.[name] , h2.[path]
    from Tree t
    join @hierarchy as h2 on h2.[path].IsDescendantOf(t.[path]) = 1 and t.[path] <> h2.[path] and h2.[path].GetLevel() - t.[level] < 2 
)
select [Level], cast(FullName as varchar(25)) [Fullname], cast(Path as varchar(10)) [Path] 
from Tree
order by Path

Output:

Level  Fullname                  Path
------ ------------------------- ----------
0      Ahmet                     /
1      Ahmet/Aliye               /1/
2      Ahmet/Aliye/Selen         /1/1/
1      Ahmet/Erdem               /2/
2      Ahmet/Erdem/Bilge         /2/1/
2      Ahmet/Erdem/Aydin         /2/2/
3      Ahmet/Erdem/Aydin/Tomrs   /2/2/2/

Upvotes: 3

Related Questions