Recursive query in MsSQL | Code Factory

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;

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

It’s Time To Streamline Your HR Operations — Using RPA

robotic process automation in hr

The [Z2A] Training — CruLoader: Stage 2 Loader and Payload (Analysis)

Build an app to practice a foreign language with AWS Amplify, Amazon Polly and Translate

How apps convert color photographs into black & white images?

How to install Docker On CentOS 8/RHEL 8

How Save Time on Tag Management and Get Back to the Real Work

Workers and Node: kue it up.

CI/CD using Jenkins and Kubernetes

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
Code Factory

Code Factory

More from Medium

This Week I Looked at Version Control

Version Control Git

Quick Git Helps — Basic Commands Refresher

How to Debug Efficiently for High-Level Languages