SQL diagram, tree, hierarchy

UDF: User-defined functions

CTE: Common table expression

SET NOCOUNT on;
Use Zhubenxi
Go
If OBJECT_ID (' dbo. Parts ') is not null
DROP TABLE dbo. Parts;
If OBJECT_ID (' dbo. BOM ') is not NULL
DROP TABLE DBO. BOM;
CREATE TABLE dbo. Parts
(
PartID int NOT null primary key,
PartName varchar (+) NOT NULL
);
INSERT INTO dbo. Parts (PartID, PartName) VALUES (1, ' Black tea '), (2, ' white Tea '), (3, ' Latte '), (4, ' Espresso '), (5, ' double Espresso '), (6, ' Cup Cover '), (7, ' regular Cup '), (8, ' stirrer '), (9, ' espresso cup '), (ten, ' tea Shot '), (one, ' milk '), (A, ' coffe shot '), (13, ' Tea leaves '), (+, ' water '), (+, ' sugar Bag '), (+, ' ground coffee '), (+, ' coffee beans ')

CREATE TABLE dbo. Bom
(
PartID int NOT NULL references dbo. Parts,
Assemblyid int NULL references dbo. Parts,
Unit varchar (3) NOT NULL,
Qty Decimal (8,2) is not NULL,
Unique (Partid,assemblyid),
Check (partid<> Assemblyid)

)
INSERT INTO dbo. BOM (PartID, Assemblyid, Unit, qty) VALUES (1,null, ' EA ', 1.00), (2,null, ' EA ', 1.00), (3,null, ' EA ', 1.00), (4,null, ' EA '), 1.00), (5,null, ' EA ', 1.00), (6,1, ' EA ', 1.00), (7,1, ' EA ', 1.00), (10,2, ' EA ', 1.00), (11,15, ' EA ', 1.00), (12,3, ' EA ', 1.00), ( 13,4, ' EA ', 1.00), (14,2, ' ML ', 205.00), (15,7, ' EA ', 13.00), (16,12, ' G ', 15.00), (17,16, ' G ', 15.00)


CREATE TABLE dbo. Cities
(
Cityid char (3) NOT null primary key,
City varchar (+) is not NULL,
Region varchar (+) NULL,
Country varchar (+) NOT NULL
);
CREATE TABLE dbo. Roads
(
City1 char (3) NOT NULL references dbo. Cities,
City2 char (3) NOT NULL references dbo. Cities,
distance int NOT NULL,
Primary KEY (City1,city2),
Check (City1<city2),
Check (distance>0)
);
INSERT INTO dbo. Cities (Cityid, city,region, country) VALUES (' FAI ', ' Atlanta ', ' GA ', ' USA '), (' ANC ', ' CHICAGO ', ' IL ', ' USA '), (' SFO ', ' DENVER ', ' Co ', ' USA '), (' SEA ', ' DENVER ', ' Co ', ' USA '), (' JFK ', ' DENVER ', ' Co ', ' USA '), (' MSP ', ' DENVER ', ' Co ', ' USA ')



 insert into dbo. Roads (city1, City2,distance) VALUES (' sea                                                                                                                                                                                            ', ' JFK ', 336, (' Sea ', ' MSP ', 3376), (' Sea ', ' IAH ', 3736), (' Sea ', ' MCI ', 3356), (' DEN ', ' MIA ', 3536), (' Den ', ' MJP ', 3336), (' Den ', ' IAH ', 336 '), (' Den ', ' MCI ', 3376)







DECLARE @ROOT as INT = 1;
With SUBS
As
(
Select empid,empname,0 as LVl,
CAST ('. ') +cast (EMPID as VARCHAR (10)) + '. ' As VARCHAR (max) as path, 0 as cycle from dbo. EMPLOYEE
where Empid [email protected]
UNION ALL
Select C.empid, C.empname, P.lvl +1, cast (P.path + cast (c.empid as varchar) + '. ' as varchar (max)),
Case is p.path like '%. ' +cast (c.empid as varchar) + '.% ' then 1 else 0 end
From subs as P joins dbo. EMPLOYEE as C on C.mgrid =p.empid and P.cycle =0
)
Select path,* from Subs where
Cycle = 1;

SQL diagram, tree, hierarchy