How to get a hierarchy table in Sql Server

Question

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

Answer 1

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