Untitled
unknown
plain_text
2 years ago
2.6 kB
5
Indexable
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;
Editor is loading...