SQL queries to manage hierarchical or parent-child relational

/*create the tbl*/
CREATE TABLE UserType(
    Id BIGINT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    ParentId BIGINT NULL  
)

/*insert data into table*/
INSERT INTO `usertype` (`Id`, `Name`, `ParentId`) VALUES
(NULL, 'user_1', NULL),
(NULL, 'user_2', '1'),
(NULL, 'user_3', '2'),
(NULL, 'user_4', '3'),
(NULL, 'user_5', '4'),
(NULL, 'user_6', '5'),
(NULL, 'user_7', '6'),
(NULL, 'user_8', '7'),
(NULL, 'user_9', '8'),
(NULL, 'user_10', '9');

/*regular join to get detail*/  
SELECT ChildUserType.Id, ChildUserType.Name, ParentUserType.Id, ParentUserType.Name
    FROM UserType AS ChildUserType
    LEFT JOIN UserType AS ParentUserType ON ChildUserType.ParentId = ParentUserType.Id;




/* all posible childs of @userTypeId */
DECLARE @userTypeId BIGINT;
SET @userTypeId = 5;
WITH tblChild AS
(
    SELECT *
        FROM UserType WHERE ParentId = @userTypeId
    UNION ALL
    SELECT UserType.* FROM UserType  JOIN tblChild  ON UserType.ParentId = tblChild.Id
)
SELECT *
    FROM tblChild
OPTION(MAXRECURSION 32767)



/*row posible parents in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL  
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
    CAST(CASE WHEN Parent.Parents = ''
        THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
        ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId  
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)



/*row posible childs in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Childs)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS LastGeneration
        WHERE Id NOT IN (SELECT COALESCE(ParentId, 0) FROM UserType)    
    UNION ALL
    SELECT PrevGeneration.Id, PrevGeneration.Name, PrevGeneration.ParentId,
    CAST(CASE WHEN Child.Childs = ''
        THEN(CAST(Child.ChildId AS VARCHAR(MAX)))
        ELSE(Child.Childs + '.' + CAST(Child.ChildId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS PrevGeneration
        INNER JOIN Hierarchy AS Child ON PrevGeneration.Id = Child.ParentId  
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)