Untitled

 avatar
unknown
plain_text
2 years ago
838 B
6
Indexable
DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10);
DECLARE @Category NVARCHAR(100) = 'Laptops'; -- Replace 'Laptops' with the desired category dynamically passed.

DECLARE @result NVARCHAR(MAX) = '';

;WITH RecursiveCTE AS (
    SELECT
        Id,
        Title,
        ParentCategoryId,
        Numbering = CAST(ROW_NUMBER() OVER (ORDER BY Id) 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.Id))
    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;
Editor is loading...