sql - Select statement to return parent and infinite children

Question

Give the table structure, as something like:

ID      ParentID      Name
1       NULL          A root
2       NULL          Another root
3       1             Child of 1
4       3                Grandchild of 1
5       4                   Great grandchild of 1
6       1             Child of 1
7       NULL          Another root
8       7             Child of 6

I am looking for an elegant (if possible) solution for a single Sql statement/function that would return all data in the table when given an ID = 1

So my result would look something like:

ID      ParentID      Name
1       NULL          A root
3       1             Child of 1
4       3                Grandchild of 1
5       4                   Great grandchild of 1
6       1             Child of 1

I've seen similar questions on SO though for the most part they only seem to be looking at a given number of levels.

This structure can, ultimately, be limitless - folder with children, with many other children

Is this possible? If so, how would I accomplish it?

Answer 1

So referencing this answer:

SQL Server CTE Parent Child recursive

Here's a working version with your schema:

Table Creation Script

CREATE TABLE YOUR_TABLE
    ([ID] int, [ParentID] int, [Name] varchar(21))
;
    
INSERT INTO YOUR_TABLE
    ([ID], [ParentID], [Name])
VALUES
    (1, NULL, 'A root'),
    (2, NULL, 'Another root'),
    (3, 1, 'Child of 1'),
    (4, 3, 'Grandchild of 1'),
    (5, 4, 'Great grandchild of 1'),
    (6, 1, 'Child of 1'),
    (7, NULL, 'Another root'),
    (8, 7, 'Child of 6')
;

Recursive CTE

DECLARE @ID INT = 1

;WITH ParentChildCTE
AS (
    SELECT ID, ParentId, Name        
    FROM YOUR_TABLE
    WHERE Id = @ID

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name        
    FROM YOUR_TABLE T1
    INNER JOIN ParentChildCTE T ON T.ID = T1.ParentID
    WHERE T1.ParentID IS NOT NULL
    )
SELECT *
FROM ParentChildCTE

The key part is in the CTE creation where the UNION ALL joins back on to the result set, joining ID to ParentId, which doesn't limit the number of level.