Untitled

DECLARE @Category NVARCHAR(100) = 'YourCategoryName'; -- Replace 'YourCategoryName' with the actual category name you want to query

DECLARE @NewLine AS VARCHAR(2) = CHAR(13) + CHAR(10);

DECLARE @result VARCHAR(MAX) = '';

;WITH RecursiveCTE AS (
    SELECT
        Id,
        Title,
        ParentCategoryId,
        Numbering = CAST(ROW_NUMBER() OVER (ORDER BY Title) AS NVARCHAR(MAX))
    FROM cte.getEshopCategories()
    WHERE Title = @Category
    UNION ALL
    SELECT
        c.Id,
        c.Title,
        c.ParentCategoryId,
        Numbering = CONCAT(p.Numbering, '.', ROW_NUMBER() OVER (PARTITION BY c.ParentCategoryId ORDER BY c.Title))
    FROM cte.getEshopCategories() c
    INNER JOIN RecursiveCTE p ON c.ParentCategoryId = p.Id
)

SELECT @result = @result + Numbering + '  ' + Title + @NewLine FROM RecursiveCTE
ORDER BY Numbering;

PRINT @result;