Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
1.1 kB
1
Indexable
Never
/*
	Use function cte.getEshopCategories() to get a hierarchy of categories.
	Calculate numbering for all categories (check expected result).
	Categories are numbered by category title.
	Print all subcategories of the declared category @Category.
*/

the above one is my sql question
and my answer is 

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;
 

but my test cases are not passing