CODEWITHSUNDEEP
sqlsql-serverhierarchy
Gonzalo
Gonzalo

Reputation: 3

How to get a hierarchy table in Sql Server

I would like to create a table that shows the hierarchy of another SQL Server table. I have a table with the following structure

+-----------+----------+
| AccountID | ParentID |
+-----------+----------+
| 1         |          |
+-----------+----------+
| 2         | 1        |
+-----------+----------+
| 3         | 1        |
+-----------+----------+
| 4         | 2        |
+-----------+----------+
| 5         | 3        |
+-----------+----------+
| 6         | 5        |
+-----------+----------+

and would like to get another table with the following structure

+-----------+------+
| AccountID | Path |
+-----------+------+
| 1         | 1    |
+-----------+------+
| 2         | 1    |
+-----------+------+
| 2         | 2    |
+-----------+------+
| 3         | 1    |
+-----------+------+
| 3         | 3    |
+-----------+------+
| 4         | 1    |
+-----------+------+
| 4         | 2    |
+-----------+------+
| 4         | 4    |
+-----------+------+
| 5         | 1    |
+-----------+------+
| 5         | 3    |
+-----------+------+
| 5         | 5    |
+-----------+------+
| 6         | 1    |
+-----------+------+
| 6         | 3    |
+-----------+------+
| 6         | 5    |
+-----------+------+
| 6         | 6    |
+-----------+------+

Note: In the Parents ID field you must always include your own ID, i.e., 1-1, 2-2, etc.

If you see in the first table, for AccountID 1, there is no ParentID, because it is the highest hierarchical level. But in the table I need to extract, you see that for AccountID 1 the value 1 appears in the Path column. The same happens for the rest of the values, that is, for AccountID 2, in the result table AccountID 1 appears (its superior hierarchical value), but it is also necessary that it includes the value 2. And so for the rest of the values in the AccountID column.

Setup sample data:

create table Account 
(
  AccountID INT,
  ParentID INT NULL
 )

 INSERT INTO Account(AccountID, ParentID)
 VALUES
 (1, NULL),
 (2,1),
 (3,1),
 (4,2),
 (5,3),
 (6,5)

I'm not able to get this results. Could you help me?

Thanks in advance

Upvotes: 0

Views: 166

Answers (2)

Gonzalo
Gonzalo

Reputation: 3

I tried with this sentence, based on your sentence,

WITH rCTe AS (
    SELECT YT.Accountid AS RootID,
           YT.Accountid,
           YT.Parentaccountid
    FROM PBI_OrganizacionJerarquica YT
    UNION ALL
    SELECT r.RootID,
           YT.Accountid,
           YT.Parentaccountid
    FROM rCTe r
         JOIN PBI_OrganizacionJerarquica YT ON r.Parentaccountid = YT.Accountid)

SELECT r.RootID AS AccountID,
       r.Accountid AS [Path]
FROM rCTe r
ORDER BY AccountId,
         [Path];

and I get this error

Msg 319, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Upvotes: 0

Thom A
Thom A

Reputation: 95554

As mentioned, the easiest way to achieve this is with a rCTE, and the recurse down each level of the hierarchy until you get to the bottom:

--Sample Data
WITH YourTable AS(
    SELECT V.AccountID,
           V.[Path]
    FROM (VALUES(1,NULL),
                (2,1),
                (3,1),
                (4,2),
                (5,3),
                (6,5))V(AccountID,[Path])),
--Solution
rCTe AS(
    SELECT YT.AccountID AS RootID,
           YT.AccountID,
           YT.[Path]
    FROM YourTable YT
    UNION ALL
    SELECT r.RootID,
           YT.AccountID,
           YT.[Path]
    FROM rCTe r
         JOIN YourTable YT ON r.[Path] = YT.AccountID)
SELECT r.RootID AS AccountID,
       r.AccountID AS [Path]
FROM rCTe r
ORDER BY AccountId,
         [Path];

DB<>Fiddle

Upvotes: 3

Related Questions