StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHierarchical CTE with additional sort column per level
text
Body
copied!<p>CTEs are a bit new to me so I am hoping someone can help with the following one which I wrote that will take a category table and build a hierarchy out of it for display. I know this kind of thing is asked all the time, but I think my situation with the sorting makes it a bit unique.</p> <p>I would expect a number of suggestions to use HierarchyID, but sadly that is not an option for a long list of reasons that are not relevant here. The solution I have come up with though works and gives me the data I expect, but I am wondering if there is a better/more elegant way of accomplishing this. </p> <p>The basic requirements are as follows:</p> <ol> <li>Categories can have an unlimited number of children</li> <li>Categories can be an unlimited number of levels deep</li> <li>Categories with the same parent will be sorted based on a "sort" field. If one is not specified (default is 0) or is the same as another sibling category, it will sorted alphabetically.</li> </ol> <p>Table Definition:</p> <pre><code>CREATE TABLE [dbo].[TreeTest] ( [id] [int] NOT NULL, [parent] [int] NULL, [title] [varchar](50) NOT NULL, [sort] [int] NOT NULL ) GO ALTER TABLE [dbo].[TreeTest] ADD CONSTRAINT [DF_TreeTest_sort] DEFAULT ((0)) FOR [sort] GO </code></pre> <p>Insert statements:</p> <pre><code>INSERT TreeTest(id,parent,title,sort) VALUES('1',NULL,'Parent 1','0') INSERT TreeTest(id,parent,title,sort) VALUES('2',NULL,'Parent 2','0') INSERT TreeTest(id,parent,title,sort) VALUES('3',NULL,'Parent 3','2') INSERT TreeTest(id,parent,title,sort) VALUES('4',NULL,'Parent 4','1') INSERT TreeTest(id,parent,title,sort) VALUES('5','1','Child 1a','0') INSERT TreeTest(id,parent,title,sort) VALUES('6','2','Child 2a','0') INSERT TreeTest(id,parent,title,sort) VALUES('7','3','Child 3a','0') INSERT TreeTest(id,parent,title,sort) VALUES('8','1','Child 1b','1') INSERT TreeTest(id,parent,title,sort) VALUES('9','1','Child 1c','2') INSERT TreeTest(id,parent,title,sort) VALUES('10','1','Child 1d','1') INSERT TreeTest(id,parent,title,sort) VALUES('11','6','Child 2a 1','0') INSERT TreeTest(id,parent,title,sort) VALUES('12','6','Child 2a 2','1') INSERT TreeTest(id,parent,title,sort) VALUES('13','6','Child 2a 3','0') INSERT TreeTest(id,parent,title,sort) VALUES('14','6','Child 2a 4','2') </code></pre> <p>CTE:</p> <pre><code>WITH TreeList (id, parent, title, sort, title_path, level_id, level_id_path) as ( SELECT p.id, p.parent, p.title, p.sort, CONVERT(nvarchar(max), p.title), ROW_NUMBER() OVER(PARTITION BY parent ORDER BY p.sort, p.title), CAST(ROW_NUMBER() OVER(PARTITION BY parent ORDER BY p.sort) AS varchar(max)) FROM TreeTest p WHERE p.parent is null UNION ALL SELECT c.id, c.parent, c.title, c.sort, r.title_path + '/' + c.title, ROW_NUMBER() OVER(PARTITION BY c.parent ORDER BY c.sort, c.title), CONVERT(varchar(max), r.level_id_path + '.' + CAST(ROW_NUMBER() OVER(PARTITION BY c.parent ORDER BY c.sort, c.title) AS VARCHAR)) FROM TreeTest AS c INNER JOIN treelist AS r ON c.parent = r.id ) SELECT * FROM TreeList ORDER BY level_id_path </code></pre> <p>Output (I figured an image was the easiest way to show the output)</p> <p><img src="https://i.stack.imgur.com/brAu6.png" alt="output"></p> <p>Again, this works according to the specifications I have, but I'm not sure about the efficiency and whether or not there is a better way to do this. When I look at the execution plan for this, it seems like the most expensive piece is the sorting/index scanning, but this seems expected given the lack of indexes in this example. If anyone has any input it would be greatly appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload