September 20, 2013 at 10:33 am
Hi,
I've a menu that has several levels and each level has a position to order the entries.
We had a cursor, recursive, that built the menu but we'd decided to change it to CTE since it's faster.
Here is a sample of the structure and data:
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'menu')
DROP TABLE menu
GO
CREATE TABLE menu (Id TINYINT NOT NULL, IdRoot TINYINT NULL, Name VARCHAR(10) NOT NULL, Position TINYINT NOT NULL)
GO
INSERT INTO menu (Id, idRoot, Name, Position) VALUES
(1, 0, '0', 1),
(4, 1, '1', 1),
(2, 1, '2', 2),
(3, 2, '2.1', 1),
(6, 3, '2.1.1', 1),
(8, 6, '2.1.1.1', 1),
(5, 2, '2.2', 2),
(9, 2, '2.3', 3),
(7, 1, '3', 3)
GO
WITH menuCTE (Id, IdRoot, Name, Position, Ord) AS (
SELECT Id, IdRoot, Name, Position, 0 FROM menu WHERE IdRoot = 0
UNION ALL
SELECT m.Id, m.IdRoot, m.Name, m.Position, mCTE.Ord + 1 FROM menu m INNER JOIN menuCTE mCTE ON m.IdRoot = mCTE.Id
)
SELECT * FROM menuCTE
The SELECT output is:
Id IdRoot Name Position Ord
---- ------ ---------- -------- -----------
1 0 0 1 0
4 1 1 1 1
2 1 2 2 1
7 1 3 3 1
3 2 2.1 1 2
5 2 2.2 2 2
9 2 2.3 3 2
6 3 2.1.1 1 3
8 6 2.1.1.1 1 4
I've tried several ORDER BY and even ROW_NUMBER PARTITION BY but can't seem to find the right combination....
The output desired, and obtained with the recursive cursor, is:
Id IdRoot Name Position Ord
---- ------ ---------- -------- -----------
1 0 0 1 0
4 1 1 1 1
2 1 2 2 1
3 2 2.1 1 2
6 3 2.1.1 1 3
8 6 2.1.1.1 1 4
5 2 2.2 2 2
9 2 2.3 3 2
7 1 3 3 1
Help please...
Thanks,
Pedro
September 20, 2013 at 12:14 pm
Like this?
order by name
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2013 at 12:48 pm
Sean Lange (9/20/2013)
Like this?
order by name
I think ORDER BY name is what you want. But to get the sort order correct you will need to pad the values with leading zeroes or level id.
0001.0002
0001.0003
0002.0010
0002.0011
0002.0012
September 21, 2013 at 1:17 am
The values that 'name' has are just for ordering porpoise, for easier visualization of the results...
'name' actually has Accounting, Sales, Purchases, Human Resources, etc...
So it can't be used for ordering...
It start with the record with IdRoot = 0 and then it children and further down the level...
The cursor works cause it gets ti IdRoot = 0, inserts it in a temp table and then it's children and for each child, inserts a record in the temp table and processes its children recursively.
Pedro
September 21, 2013 at 10:06 am
PiMané (9/21/2013)
The values that 'name' has are just for ordering porpoise, for easier visualization of the results...'name' actually has Accounting, Sales, Purchases, Human Resources, etc...
If the "Name" column actually contains something else in real life, please post the test data so that it more closely resembles the real data.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2013 at 1:01 am
Here is the code with some "real" data...
The menu can be customized by the used, including the name (it's an alias).
The table has other columns, like the original Id from our menu, but for the case it's unnecessay.
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'menu')
DROP TABLE menu
GO
/*
Id - the menu entry Id
IdRoot - the menu entry root Id (father)
Name - description for the entry
Position - the position of the entry on it's father (1st child, 2nd child, ....)
*/
CREATE TABLE menu (Id TINYINT NOT NULL, IdRoot TINYINT NULL, Name VARCHAR(10) NOT NULL, Position TINYINT NOT NULL)
GO
INSERT INTO menu (Id, idRoot, Name, Position) VALUES
(1, 0, 'ERP', 1),
(4, 1, 'ACC', 1),
(2, 1, 'HR', 2),
(3, 2, 'PAYMENT', 1),
(6, 3, 'PROCESS', 1),
(8, 6, 'CANCEL', 1),
(5, 2, 'VACATIONS', 2),
(9, 2, 'ABSENTS', 3),
(7, 1, 'SALES', 3)
GO
WITH menuCTE (Id, IdRoot, Name, Position, Ord) AS (
SELECT Id, IdRoot, Name, Position, 0 FROM menu WHERE IdRoot = 0
UNION ALL
SELECT m.Id, m.IdRoot, m.Name, m.Position, mCTE.Ord + 1 FROM menu m INNER JOIN menuCTE mCTE ON m.IdRoot = mCTE.Id
)
SELECT * FROM menuCTE
The SELECT output is:
Id IdRoot Name Position Ord
---- ------ ---------- -------- -----------
1 0 ERP 1 0
2 1 HR 2 1
3 2 PAYMENT 1 2
4 1 ACC 1 1
5 2 VACATIONS 2 2
6 3 PROCESS 1 3
7 1 SALES 3 1
8 6 CANCEL 1 4
9 2 ABSENTS 3 2
The output desired is:
Id IdRoot Name Position Ord
---- ------ ---------- -------- -----------
1 0 ERP 1 0
4 1 ACC 1 1
2 1 HR 2 1
3 2 PAYMENT 1 2
6 3 PROCESS 1 3
8 6 CANCEL 1 4
5 2 VACATIONS 2 2
9 2 ABSENTS 3 2
7 1 SALES 3 1
Thanks,
Pedro
September 22, 2013 at 9:37 am
According to the ID and IDRoot of the test data, your desired output seems to be a bit off if you want these menu items to be sorted in order by level and a name.
The "Position" and "Ord" columns were also a bit confusing as to their purpose so, rather than including those columns, I included some more-obviously-named columns. Rename them as you see fit.
I also included some other columns just for the purpose of furthering understanding of what the code does. Feel free to delete them from the output (the final SELECT in the code) as you see fit but don't delete them from the CTE because the code will no longer work properly if you do.
With all of that in mind, I believe your desired output should be...
ID IDRoot Name MenuLevel DisplayOrder IndentedDisplay HierarchicalPath
-- ------ --------- --------- ------------ ---------------------- ----------------------------------------------
1 0 ERP 1 1 ERP ERP
4 1 ACC 2 2 ACC ERP ACC
2 1 HR 2 3 HR ERP HR
9 2 ABSENTS 3 4 ABSENTS ERP HR ABSENTS
3 2 PAYMENT 3 5 PAYMENT ERP HR PAYMENT
6 3 PROCESS 4 6 PROCESS ERP HR PAYMENT PROCESS
8 6 CANCEL 5 7 CANCEL ERP HR PAYMENT PROCESS CANCEL
5 2 VACATIONS 3 8 VACATIONS ERP HR VACATIONS
7 1 SALES 2 9 SALES ERP SALES
This is the code that produced that output in order according to the given relationship of ID and IDRoot and sorted by Name within those formed levels you had in your example data.
WITH
cteMenu AS
(
SELECT ID, IDRoot, Name, MenuLevel = 1,
HierarchicalPath = CAST(CAST(Name AS CHAR(10)) AS VARCHAR(8000))
FROM dbo.Menu
WHERE IDRoot = 0
UNION ALL
SELECT e.ID, e.IDRoot, e.Name, MenuLevel = d.MenuLevel + 1,
HierarchicalPath = CAST(HierarchicalPath + CAST(e.Name AS CHAR(10)) AS VARCHAR(8000))
FROM dbo.Menu e
INNER JOIN cteMenu d ON e.IDRoot = d.ID
)
SELECT ID, IDRoot, Name, MenuLevel,
DisplayOrder = ROW_NUMBER() OVER (ORDER BY HierarchicalPath),
IndentedDisplay = CAST(REPLICATE(SPACE(4),MenuLevel-1) + Name AS VARCHAR(50)),
HierarchicalPath
FROM cteMenu
ORDER BY DisplayOrder
;
For more information on creating and using a Hierarchical Path for purposes of sorting along with some other grand tricks of the trade for handling DAG (Directed Acyclic Graph) hierarchies, please see the following articles.
[font="Arial Black"]Displaying Sorted Hierarchies (SQL Spackle)
[/font][/url][font="Arial Black"]Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
[/font][/url][font="Arial Black"]Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations
[/font][/url]
Finally, you need to know that calculating menus on-the-fly like this is VERY resource intensive on high usage systems. Everytime you pull-down the menu, it has to make a round trip to the server. It would be much better to cache the menu information and put a trigger on the menu table to update the cache ONLY when the menu data suffers a change.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2013 at 2:53 pm
One way to accomplish this is using a varbinary column to concatenate the sequence of numbers based on the position value.
WITH C1 AS (
SELECT
Id, IdRoot, Name, Position,
CAST(ROW_NUMBER() OVER(ORDER BY position) AS varbinary(900)) AS order_val,
0 AS lvl
FROM
menu
WHERE
IdRoot = 0
UNION ALL
SELECT
C.Id, C.IdRoot, C.Name, C.Position,
CAST(P.order_val + CAST(ROW_NUMBER() OVER(PARTITION BY P.IdRoot ORDER BY C.Position) AS BINARY(8)) AS varbinary(900)),
P.lvl + 1
FROM
C1 AS p
INNER JOIN
menu AS C
ON p.Id = C.IdRoot
)
SELECT REPLICATE(SPACE(4), 2 * lvl) + [name] AS menu
FROM C1
ORDER BY order_val;
GO
/*
menu
ERP
ACC
HR
PAYMENT
PROCESS
CANCEL
VACATIONS
ABSENTS
SALES
*/
To generate the numbers in specific order we can use a ranking function like ROW_NUMBER, where you can use multiple columns to drive the sort order (by name or position or whatever other combination).
You can read more about other uses of he ranking functions in the last book from Itzik Ben-Gan about T-SQL Querying.
Inside Microsoft® SQL Server® 2008: T-SQL Querying
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
September 22, 2013 at 5:22 pm
NM.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2013 at 3:03 am
hunchback (9/22/2013)
One way to accomplish this is using a varbinary column to concatenate the sequence of numbers based on the position value.
WITH C1 AS (
SELECT
Id, IdRoot, Name, Position,
CAST(ROW_NUMBER() OVER(ORDER BY position) AS varbinary(900)) AS order_val,
0 AS lvl
FROM
menu
WHERE
IdRoot = 0
UNION ALL
SELECT
C.Id, C.IdRoot, C.Name, C.Position,
CAST(P.[path] + CAST(ROW_NUMBER() OVER(PARTITION BY P.IdRoot ORDER BY C.Position) AS BINARY(8)) AS varbinary(900)),
P.lvl + 1
FROM
C1 AS p
INNER JOIN
menu AS C
ON p.Id = C.IdRoot
)
SELECT REPLICATE(SPACE(4), 2 * lvl) + [name] AS menu
FROM C1
ORDER BY order_val;
GO
/*
menu
ERP
ACC
HR
PAYMENT
PROCESS
CANCEL
VACATIONS
ABSENTS
SALES
*/
To generate the numbers in specific order we can use a ranking function like ROW_NUMBER, where you can use multiple columns to drive the sort order (by name or position or whatever other combination).
You can read more about other uses of he ranking functions in the last book from Itzik Ben-Gan about T-SQL Querying.
Inside Microsoft® SQL Server® 2008: T-SQL Querying
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
Thanks, like a charm...
With a 28 entry menu (very small one) take half the time and does half the reads....
Pedro
September 23, 2013 at 3:22 am
hunchback (9/22/2013)
One way to accomplish this is using a varbinary column to concatenate the sequence of numbers based on the position value.
WITH C1 AS (
SELECT
Id, IdRoot, Name, Position,
CAST(ROW_NUMBER() OVER(ORDER BY position) AS varbinary(900)) AS order_val,
0 AS lvl
FROM
menu
WHERE
IdRoot = 0
UNION ALL
SELECT
C.Id, C.IdRoot, C.Name, C.Position,
CAST(P.[path] + CAST(ROW_NUMBER() OVER(PARTITION BY P.IdRoot ORDER BY C.Position) AS BINARY(8)) AS varbinary(900)),
P.lvl + 1
FROM
C1 AS p
INNER JOIN
menu AS C
ON p.Id = C.IdRoot
)
SELECT REPLICATE(SPACE(4), 2 * lvl) + [name] AS menu
FROM C1
ORDER BY order_val;
GO
/*
menu
ERP
ACC
HR
PAYMENT
PROCESS
CANCEL
VACATIONS
ABSENTS
SALES
*/
To generate the numbers in specific order we can use a ranking function like ROW_NUMBER, where you can use multiple columns to drive the sort order (by name or position or whatever other combination).
You can read more about other uses of he ranking functions in the last book from Itzik Ben-Gan about T-SQL Querying.
Inside Microsoft® SQL Server® 2008: T-SQL Querying
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
As Jeff said, NM=Nice Method (?).
Except I think that P.[Path] should be p.Order_val in the recursive leg of your CTE.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 23, 2013 at 3:26 am
dwain.c (9/23/2013)
As Jeff said, NM=Nice Method (?).Except I think that P.[Path] should be p.Order_val in the recursive leg of your CTE.
Yes, had to change it to work but it's just fine... 🙂
Pedro
September 23, 2013 at 3:29 am
PiMané (9/23/2013)
dwain.c (9/23/2013)
As Jeff said, NM=Nice Method (?).Except I think that P.[Path] should be p.Order_val in the recursive leg of your CTE.
Yes, had to change it to work but it's just fine... 🙂
Pedro
Figured you must've.
I looked at this in the morning but didn't have time, so I came back to study it. Any time Jeff approves of something it makes me want to understand.
Now I think I do so I'm probably the better for it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 23, 2013 at 5:40 am
I changed [path] by order_val in the recursive part and now it should run flawless.
A recursive CTE is still an iterative approach so I do not hold my breath hoping for better performance.
You can add a clustered index by ID and a nonclustered by IdRoot.
September 23, 2013 at 5:43 am
hunchback (9/23/2013)
I changed [path] by order_val in the recursive part and now it should run flawless.A recursive CTE is still an iterative approach so I do not hold my breath hoping for better performance.
You can add a clustered index by ID and a nonclustered by IdRoot.
It's better than the recursive cursor it's currently using...
Instead of 7ms and 530reads, for 28 rows, takes 3ms and 321 reads.
And for bigger menus the difference is even greater.
Pedro
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply