Recursive query in MsSQL | Code Factory

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;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store