CTE is a little new to me, so I hope someone can help with the next one that I wrote, which will take a category table and build a hierarchy from it for display. I know that this kind of questions is asked all the time, but I think my sorting situation makes it a little unique.
I would expect a number of suggestions for using HierarchyID, but, unfortunately, this is not an option for a long list of reasons that are not relevant here. The solution I came up with, although it works, gives me the data I expect, but I wonder if there is a better / more elegant way to achieve this.
The main requirements are as follows:
- Categories can have an unlimited number of children.
- Categories can be unlimited levels deep
- Categories with the same parent will be sorted based on the sort field. If one is not specified (default is 0) or matches another category, it will be sorted in alphabetical order.
Table definition:
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
Insert statements:
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')
CTE:
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
Output (I realized that the image was the easiest way to display the output)
Again, this works according to the specifications that I have, but I'm not sure about the effectiveness and if there is a better way to do this. When I look at the execution plan for this, it seems that the most expensive part is sorting / index scanning, but this seems to be expected, given the lack of indexes in this example. If anyone has any input, we will be very grateful.