Recursive query in MsSQL | Code Factory

Code Factory

·

Follow

2 min read

·

Apr 9, 2020

--

Listen

Share

Reference Link : Link

Donate : Link

CREATE TABLE menu_master (
    id int NOT NULL,
    menu_name varchar(45) NOT NULL,
    menu_action varchar(45) NOT NULL,
    parent_id int NOT NULL,
    menu_order int DEFAULT NULL,
    PRIMARY KEY (id)
);INSERT INTO menu_master VALUES 
    (1,'Menu','/menu',0,1),
    (2,'Menu1','/menu1',1,2),
    (3,'Master','/master',0,2),
    (4,'Menu2','/menu2',1,1),
    (5,'Menu1.1','/menu11',2,1),
    (6,'Menu1.2','/menu12',2,2),
    (7,'Menu1.4','/menu14',2,3),
    (8,'Menu1.3','/menu13',2,4),
    (9,'Menu2.1','/menu21',4,1),
    (10,'Menu1.1.1','/menu111',5,1);WITH rec_tree AS (
    SELECT M1.id,
        CAST(M1.menu_name AS varchar(20)) AS MENU_NAME,
        M1.menu_action,
        M1.parent_id,
        M1.menu_order,
        1 as level,
        CAST('' AS varchar(20)) AS dash_line,
        CAST(M1.id AS VARCHAR(20)) as path_info
    FROM menu_master M1
        where M1.parent_id = 0
    UNION ALL
    SELECT p.id,
        CAST(C.dash_line + CAST('--' + CAST(p.menu_name AS varchar(20)) as varchar(20)) AS VARCHAR(20)),
        p.menu_action,
        p.parent_id,
        p.menu_order,
        c.level + 1,
        CAST(C.dash_line + CAST(('--') as varchar(20)) AS VARCHAR(20)),
        CAST(C.path_info + CAST(p.menu_order as varchar(20)) AS varchar(20))
    FROM menu_master p 
    JOIN rec_tree C ON p.parent_id = C.id
)
SELECT * FROM rec_tree order by path_info;

WITH rec_tree AS (
    SELECT M1.id,
        CAST(M1.menu_name AS varchar(20)) AS MENU_NAME,
        M1.menu_action,
        M1.parent_id,
        M1.menu_order,
        1 as level,
        CAST('' AS varchar(20)) AS dash_line,
        CAST(M1.id AS VARCHAR(20)) as path_info
    FROM menu_master M1
        where M1.parent_id = 0
    UNION ALL
    SELECT p.id,
        CAST(C.dash_line + CAST('--' + CAST(p.menu_name AS varchar(20)) as varchar(20)) AS VARCHAR(20)),
        p.menu_action,
        p.parent_id,
        p.menu_order,
        c.level + 1,
        CAST(C.dash_line + CAST(('--') as varchar(20)) AS VARCHAR(20)),
        CAST(C.path_info + CAST(p.menu_order as varchar(20)) + CAST(p.id as varchar(20)) AS varchar(20))
    FROM menu_master p 
    JOIN rec_tree C ON p.parent_id = C.id
)
SELECT * FROM rec_tree order by path_info;