Untitled

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

-- Query for 'All Categories'
DECLARE @CategoryAll NVARCHAR(100) = 'All Categories';
DECLARE @resultAll VARCHAR(MAX) = '';

;WITH RecursiveCTEAll AS (
    SELECT
        Id,
        Title,
        ParentCategoryId,
        Numbering = CAST(ROW_NUMBER() OVER (ORDER BY Title) AS NVARCHAR(MAX))
    FROM cte.getEshopCategories()
    WHERE Title = @CategoryAll
    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 RecursiveCTEAll p ON c.ParentCategoryId = p.Id
)

SELECT @resultAll = @resultAll + Numbering + '  ' + Title + @NewLine FROM RecursiveCTEAll
ORDER BY Numbering;

-- Query for 'Laptops'
DECLARE @CategoryLaptops NVARCHAR(100) = 'Laptops';
DECLARE @resultLaptops VARCHAR(MAX) = '';

;WITH RecursiveCTELaptops AS (
    SELECT
        Id,
        Title,
        ParentCategoryId,
        Numbering = CAST(ROW_NUMBER() OVER (ORDER BY Title) AS NVARCHAR(MAX))
    FROM cte.getEshopCategories()
    WHERE Title = @CategoryLaptops
    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 RecursiveCTELaptops p ON c.ParentCategoryId = p.Id
)

SELECT @resultLaptops = @resultLaptops + Numbering + '  ' + Title + @NewLine FROM RecursiveCTELaptops
ORDER BY Numbering;

-- Query for 'Smartphones'
DECLARE @CategorySmartphones NVARCHAR(100) = 'Smartphones';
DECLARE @resultSmartphones VARCHAR(MAX) = '';

;WITH RecursiveCTESmartphones AS (
    SELECT
        Id,
        Title,
        ParentCategoryId,
        Numbering = CAST(ROW_NUMBER() OVER (ORDER BY Title) AS NVARCHAR(MAX))
    FROM cte.getEshopCategories()
    WHERE Title = @CategorySmartphones
    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 RecursiveCTESmartphones p ON c.ParentCategoryId = p.Id
)

SELECT @resultSmartphones = @resultSmartphones + Numbering + '  ' + Title + @NewLine FROM RecursiveCTESmartphones
ORDER BY Numbering;

-- Print results for each category
PRINT 'All Categories: ' + @NewLine + @resultAll;
PRINT 'Laptops: ' + @NewLine + @resultLaptops;
PRINT 'Smartphones: ' + @NewLine + @resultSmartphones;