July 5, 2007 at 10:18 am
Hi,
I'm a SQL newbie; I've inherited a DB from my predecessor. I have a DB with a table that describes a hierarchical menu structure, which looks like this when sorted on ParentID,Name:
ID ParentID TreeOrder Name
---- -------- --------- ----
1 NULL 5 A1
2 NULL 2 B1
3 NULL 0 C1
4 NULL 1 D1
5 NULL 3 E1
6 NULL 4 F1
7 1 0 A2
8 1 3 B2
9 1 2 C2
10 1 1 D2
11 2 0 A3
12 2 2 B3
13 2 1 C3
14 11 1 A4
15 11 0 B4
(ParentID = NULL for top level, else = ID of ParentItem)
(The ID field is auto number, simplified here for ease of reading)
The above was created using:
select ID,ParentID,TreeOrder,Name,Description from dbo.tbl_MenuItems order by ParentID,Name
The sorted menu structure looks something like this:
A1/A2
A1/B2
A1/C2
A1/D2
B1/A3/A4
B1/A3/B4
B1/B3
B1/C3
C1
D1
E1
F1
Q: The application display order is based on the TreeOrder field, how can I update this field based on the sort ParentID,Name
The updated table should like this:
ID ParentID TreeOrder Name
---- -------- --------- ----
1 NULL 0 A1
2 NULL 1 B1
3 NULL 2 C1
4 NULL 3 D1
5 NULL 4 E1
6 NULL 5 F1
7 1 0 A2
8 1 1 B2
9 1 2 C2
10 1 3 D2
11 2 0 A3
12 2 1 B3
13 2 2 C3
14 11 0 A4
15 11 1 B4
Forgive the long winded post, I'm new to this.
Any ideas?
Thanks,
Pascal
July 5, 2007 at 10:50 am
Maybe:
UPDATE T
SET TreeOrder = D.TreeOrder
FROM YourTable T
JOIN (
SELECT T1.[ID]
,ROW_NUMBER( ) OVER (PARTITION BY T1.ParentID ORDER BY T1.[Name]) AS TreeOrder
FROM YourTable T1
) D
ON T.[ID] = D.[ID]
July 5, 2007 at 11:14 am
Hi,
That was spot on, works perfectly.
Thanks,
Pascal
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply