StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Recursive Menu Sorting
text
Body
copied!<p>I've got a simple table that I'm using to represent a hierarchy of categories.</p> <pre><code>CREATE TABLE [dbo].[Categories]( [ID] [int] IDENTITY(1,1) NOT NULL, [Title] [varchar](256) NOT NULL, [ParentID] [int] NOT NULL, CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('All', 0) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Banking', 8) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('USAA Checking', 2) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('USAA Mastercard', 2) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Medical', 8) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Jobs', 8) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Archive', 1) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Active', 1) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('BoA Amex', 2) </code></pre> <p>Everything is fine except for selecting the entire tree. Here is my query, I removed my ORDER BY because it doesn't work:</p> <pre><code>WITH CategoryTree (ID, Title, Level, ParentID) AS ( SELECT r.ID, r.Title, 0 Level, r.ParentID FROM Categories r WHERE r.ParentID = 0 UNION ALL SELECT c.ID, c.Title, p.Level + 1 AS Level, c.ParentID FROM Categories c INNER JOIN CategoryTree p ON p.ID = c.ParentID ) SELECT ID, REPLICATE('-----', Level) + Title AS Title, ParentID FROM CategoryTree </code></pre> <p>Results:</p> <pre><code>ID Title ParentID 1 All 0 7 -----Archive 1 8 -----Active 1 2 ----------Banking 8 5 ----------Medical 8 6 ----------Jobs 8 3 ---------------USAA Checking 2 4 ---------------USAA Mastercard 2 9 ---------------BoA Amex 2 </code></pre> <p>The result I want is this:</p> <pre><code>ID Title ParentID 1 All 0 8 -----Active 1 2 ----------Banking 8 9 ---------------BoA Amex 2 3 ---------------USAA Checking 2 4 ---------------USAA Mastercard 2 6 ----------Jobs 8 5 ----------Medical 8 7 -----Archive 1 </code></pre> <p>What is killing me is I got this working perfectly before but then I forgot to back up the DB and lost it in a server upgrade.</p> <p>I looked at the HierarchyID type in 2008 but it just seems like a big pain in the ass if you care about order of children at the same level.</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