Hàm trả về nút trước đó (do ta chỉ định) chứa nút hiện hành trong cấu trúc cây hierarchyid

-- Hàm trả về nút trước đó (do ta chỉ định) chứa nút hiện hành trong cấu trúc cây hierarchyid --

-- by code4viet --

-- Mục đích ta có thể lấy nút cha hoặc trước đó nữa nếu muốn --

-- Node.GetAncestor(n)

-- n=0 thì trả về chính nút hiện hành

-- n=1 thì trả về nút cha của nút hiện hành

-- n=2 thì trả về nút cha của nút cha của nút hiện hành

-- ...

-- nếu chỉ định n> độ sâu của nút thì trả về null

declare @code4viet_newchildren  table

(

    ID int,

    ParentID int,

    Num int,

    OrgNode hierarchyid,

    LogicalNode nvarchar(max)

);

declare @code4viet_children  table

   (

    ID int,

    ParentID int,

    Num int  

);

-- dữ liệu mẫu --

INSERT @code4viet_children

    (ID, ParentID, Num)

    select 1, NULL , 1

union all

    select 2, 1, 1

union all

    select 16, 1, 2

union all

    select 25, 1, 3

union all

    select 234, 1, 4

union all

    select 263, 1, 5

union all

    select 273, 1, 6

union all

    select 3, 2, 1

union all

    select 4, 3, 1

union all

    select 5, 3, 2

union all

    select 6, 3, 3

union all

    select 7, 3, 4

union all

    select 300, NULL , 1

union all

    select 301, 300, 1

;

-- kiểm tra dữ liệu mẫu --

-- select * from @code4viet_children;

-- tạo dữ liệu theo cấu trúc mới --

WITH

    paths(path, ID)

    AS

    (

        -- Lấy giá trị nút gốc

                    SELECT hierarchyid::GetRoot() AS OrgNode, ID

            FROM @code4viet_children AS C

            WHERE ParentID IS NULL

        UNION ALL

            -- Phần này cung cấp các giá trị cho tất cả các nút ngoại trừ nút gốc

            SELECT

                CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),

                C.ID

            FROM @code4viet_children AS C

                JOIN paths AS p

                ON C.ParentID = P.ID

    )

-- điền dữ liệu mới vào bảng --

insert into @code4viet_newchildren

    (OrgNode,LogicalNode,ID,ParentID,Num)

select a.[path], a.[path].ToString() AS LogicalNode, ID=a.ID, b.ParentID, b.Num

from paths a inner join @code4viet_children b on a.ID=b.ID;

-- kiểm tra dữ liệu --

select N'-- dữ liệu gốc --'

select *

from @code4viet_newchildren

order by LogicalNode

select N'-- dữ liệu bao gồm trường nút cha --'

-- chỉ lấy dữ liệu là nút gốc --

select [ParentOrgNode]=OrgNode.GetAncestor(1), [Level]=OrgNode.GetLevel(), *

from @code4viet_newchildren

order by LogicalNode