Using hierarchyID

  • Hell community,

    This is my first to try to use hierarchyID, but on my example above my column PATH return always NULL.

    What i  am doing wrong !??

    CREATE TABLE #tbl(
    ID int,
    ParentId INT,
    Name VARCHAR(30),
    [path] hierarchyid null
    );

    --DROP TABLE #tbl

    INSERT INTO #tbl VALUES
    (1, Null, 'Corporate_HQ',null),
    (2, 1, 'South_Region',null),
    (3, 1, 'North_Region',null),
    (4, 1, 'East_Region',null),
    (5, 1, 'West_Region',null),
    (6, 3, 'Chicago_District',null),
    (7, 3, 'Milwaukee_District',null),
    (8, 3, 'Minneapolis_District',null),
    (9, 6, 'Gold_Coast_Dealer',null),
    (10, 6, 'Blue_Island_Dealer',null);


    with cte as (
    select *,
    --cast(concat('/', ID, '/') as varchar(max)) as [path]
    CAST(concat(path.ToString() COLLATE DATABASE_DEFAULT, ID , '/') AS varchar(max)) AS [PATH]
    from #tbl
    where [ParentID] is null

    union all

    select child.*,
    cast(concat(CONVERT(VARCHAR,parent.path ) COLLATE DATABASE_DEFAULT, child.ID , '/') as varchar(max)) as [path]
    from #tbl as child
    join cte as parent
    on child.ParentID = parent.ID
    )

    update t
    set path = c.path
    from #tbl as t
    join cte as c
    on t.ID = c.ID;


    SELECT * FROM #tbl t
  • Well, I'm not really experienced with hierarchyid other than fiddling around with it.  Most of what can be accomplished with hierarchyid can also be done in various other ways.  In some projects we use nested JSON to store hierarchical data.  The code you posted had a few issues.  For clarity I created 2 tables.  The first renames your 'tbl' table as 'adjacencies' and removes the NULL column for [path].  Then the 'hierarchies' table contains the calculated hierarchyid as [path].

    drop table if exists #adjacencies;
    go
    create table #adjacencies(
    id int,
    parentid int,
    area_name varchar(30));
    go

    INSERT INTO #adjacencies VALUES
    (1, Null, 'Corporate_HQ'),
    (2, 1, 'South_Region'),
    (3, 1, 'North_Region'),
    (4, 1, 'East_Region'),
    (5, 1, 'West_Region'),
    (6, 3, 'Chicago_District'),
    (7, 3, 'Milwaukee_District'),
    (8, 3, 'Minneapolis_District'),
    (9, 6, 'Gold_Coast_Dealer'),
    (10, 6, 'Blue_Island_Dealer');

    drop table if exists #hierarchies;
    go
    create table #hierarchies(
    id int,
    parentid int,
    area_name varchar(30),
    [path] hierarchyid);

    with recur_cte(ID, ParentId, area_name, [path]) as (
    select ID, ParentId, area_name,
    cast(concat('/', ID, '/') as hierarchyid)
    from #adjacencies
    where [ParentID] is null
    union all
    select child.ID, child.ParentId, child.area_name,
    cast(concat(parent.[path].ToString(), child.ID, '/') as hierarchyid)
    from #adjacencies as child
    join recur_cte as parent on child.ParentID = parent.ID)
    insert into #hierarchies
    select *
    from recur_cte;

    select *, [path].ToString() as path_string,
    [path].GetLevel() as path_level,
    [path].GetAncestor(1).ToString() as path_ancestor_1,
    [path].GetAncestor(2).ToString() as path_ancestor_2
    from #hierarchies;

    Output

    id	parentid	area_name	path		path_string	path_level	path_ancestor_1	path_ancestor_2
    1 NULL Corporate_HQ 0x58 /1/ 1 / NULL
    2 1 South_Region 0x5B40 /1/2/ 2 /1/ /
    3 1 North_Region 0x5BC0 /1/3/ 2 /1/ /
    4 1 East_Region 0x5C20 /1/4/ 2 /1/ /
    5 1 West_Region 0x5C60 /1/5/ 2 /1/ /
    6 3 Chicago_District 0x5BE5 /1/3/6/ 3 /1/3/ /1/
    7 3 Milwaukee_District 0x5BE7 /1/3/7/ 3 /1/3/ /1/
    8 3 Minneapolis_District 0x5BE880 /1/3/8/ 3 /1/3/ /1/
    9 6 Gold_Coast_Dealer 0x5BE5A6 /1/3/6/9/ 4 /1/3/6/ /1/3/
    10 6 Blue_Island_Dealer 0x5BE5AA /1/3/6/10/ 4 /1/3/6/ /1/3/

    To get ancestors (grandchildren) for a particular area

    declare @CurrentArea hierarchyid;  

    select @CurrentArea = [path]
    from #hierarchies
    where area_name = 'Corporate_HQ'

    select *, [path].ToString() as path_string,
    [path].GetLevel() as path_level
    from #hierarchies
    where [path].GetAncestor(2) = @CurrentArea;

    Output

    id	parentid	area_name		path		path_string	path_level
    6 3 Chicago_District 0x5BE5 /1/3/6/ 3
    7 3 Milwaukee_District 0x5BE7 /1/3/7/ 3
    8 3 Minneapolis_District 0x5BE880 /1/3/8/ 3

    It's based on a Stack Overflow answer I bookmarked.  It would be interesting to learn how you intend to use this.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Hello Steve,

    Many thanks for your great explanation an examples that you send.

    I plan to use hierarchies to classify my accounting chart of accounts. I am using Power bi to create financial analysis, namely the balance sheet and income statement. I know that in Power bi using DAX I can create hierarchies, however as all my clients' plans are different, having the query of SQL already prepared for this makes everything easier.

    I happened to see another example that doesn't use HierachyID, on Stackoverflow :

    https://stackoverflow.com/questions/36784211/select-all-hierarchy-level-and-below-sql-server

    but my point is that I don't want to assign a value to a variable to start creating hierarchies for all accounts in my chart of accounts.

    Any ideas for that?

    Many thanks,

    Luis

  • luissantos wrote:

    but my point is that I don't want to assign a value to a variable to start creating hierarchies for all accounts in my chart of accounts. Any ideas for that?

    Because many accounts would have the same hierarchyid?  It depends on how the adjacencies are organized.  Maybe the unique hierarchies could be calculated in a temp table and then joined back to the accounts table where the hierarchyid could be used for different aggregations.

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Personally, I don't care for the use of HierarchyID nor it's particular form of positional notation.  It's also a bugger to fix if something goes wrong with it.

    Perhaps the following would be the better way to go because it provides the ease-of-maintenance of an Adjacency List, the strange and wonderful capabilities of a Hierarchical Path, and the nasty fast performance of Nested Sets.  Part 2 actually creates a kind of hierarchical data warehouse.  Both sets of code have been recently tested on modern hardware and both can create the results of a million node "proper" Adjacency List to all the other stuff in 19 seconds flat.

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello Steve,

     

    Thanks for your reply.

    Best regards,

    Luis

  • Hello Jeff

     

    Many thanks for all the documentation that you send me and your useful advise.

    I will try the scripts to see which one fits best what I intend to do, and also understand better how to do it

    Best regards,

    Luis

  • Every chart of accounts I have ever seen uses a hierarchical encoding scheme. I'm going to assume you've been to all library and seen the Dewey Decimal Classification codes for books. There is no need for using a rather ugly proprietary feature, like hierarchyID in SQL Server. Your company probably has such a scheme in place if they use any of the standard bookkeeping packages. If not, you can go on the Internet and find books that list such schemes..

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • @luissantos ,

    Joe's response brings up an excellent point... why not simply buy a proven product that does all of what you've asked plus so much more?  For example (although I've not personally used it for years and they may have changed the nabe), "Quick Books" did all the classic accounting (such as "double entry", etc) and also has the ability to print checks, etc, etc.  IIRC, it also is capable of printing quarterly tax documents and some decent payroll functionality, if needed.

    And, if absolutely required, it'll do exports that can be imported into SQL Server to support Power BI, etc, although it also has some pretty good charting capabilities itself.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply