Display Parent Child Hierarchy through SQL Query

SELECT Id, Title, (SELECT COUNT(*) AS Expr1 FROM Category WHERE (CategoryParent_Id = C.Id)) AS childnodecount FROM Category AS C WHERE (Id = (SELECT Id FROM Category AS Category_1 WHERE (Id = @parentId) AND (CategoryParent_Id IS NULL))) OR (CategoryParent_Id = @parentID) AND (IsActive = 1) AND (IsDeleted = 0)