Build menu with CTE ....

  • 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...

  • 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/

  • 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

  • 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...

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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...

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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...

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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



    If you need to work better, try working less...

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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.

  • 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



    If you need to work better, try working less...

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply