Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Build menu with CTE .... Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 10:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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




If you need to work better, try working less...
Post #1496959
Posted Friday, September 20, 2013 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 13,058, Visits: 11,884
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1496989
Posted Friday, September 20, 2013 12:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1497001
Posted Saturday, September 21, 2013 1:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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




If you need to work better, try working less...
Post #1497118
Posted Saturday, September 21, 2013 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497143
Posted Sunday, September 22, 2013 1:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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




If you need to work better, try working less...
Post #1497190
Posted Sunday, September 22, 2013 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
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.
Displaying Sorted Hierarchies (SQL Spackle)
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497211
Posted Sunday, September 22, 2013 2:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 114, Visits: 602
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



Post #1497237
Posted Sunday, September 22, 2013 5:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
NM.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497245
Posted Monday, September 23, 2013 3:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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




If you need to work better, try working less...
Post #1497300
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse