|
CREATE TABLE [dbo].[TBL_TREE_HIERARCHY]
(
[NODE_ID] [INT] IDENTITY(1,1) NOT NULL,
[NODE_DESCRIPTION] [NVARCHAR](50) NOT NULL,
[UNDER_PARENT] [INT] NOT NULL
)
CREATE PROCEDURE [dbo].[SSP_GET_HIERARCHY] AS
BEGIN
WITH ITERATE_NODES_RECURSIVE AS
(
SELECT NODE_DESCRIPTION, NODE_ID, UNDER_PARENT, 0 AS LEVEL_DEPTH
FROM dbo.TBL_TREE_HIERARCHY
WHERE (NODE_ID IN(SELECT NODE_ID FROM dbo.TBL_TREE_HIERARCHY
WHERE (UNDER_PARENT = 0)))
UNION ALL
SELECT Super.NODE_DESCRIPTION, Super.NODE_ID,Super.UNDER_PARENT,
Sub.LEVEL_DEPTH + 1 AS LEVEL_DEPTH
FROM dbo.TBL_TREE_HIERARCHY AS Super INNER JOIN ITERATE_NODES_RECURSIVE AS SUB ON
SUB.NODE_ID = SUPER.UNDER_PARENT
)
SELECT NODE_DESCRIPTION, NODE_ID, CONVERT(INT, UNDER_PARENT) AS UNDER_PARENT, LEVEL_DEPTH
FROM ITERATE_NODES_RECURSIVE
ORDER BY LEVEL_Depth
END
exec [SSP_GET_HIERARCHY]
insert into [TBL_TREE_HIERARCHY] values('Mathematics',0)
insert into [TBL_TREE_HIERARCHY] values('Algebra',1)
insert into [TBL_TREE_HIERARCHY] values('Geometry',1)
insert into [TBL_TREE_HIERARCHY] values('Triangle',3)
insert into [TBL_TREE_HIERARCHY] values('By Relative Length',4)
insert into [TBL_TREE_HIERARCHY] values('By Internal Angle',4)
insert into [TBL_TREE_HIERARCHY] values('Equilateral Triangle',5)
insert into [TBL_TREE_HIERARCHY] values('Scalene Triangle',5)
insert into [TBL_TREE_HIERARCHY] values('Isosceles Triangle',5)
insert into [TBL_TREE_HIERARCHY] values('Oblique > 90 Degree:Obtuse Angled Traingle',6)
insert into [TBL_TREE_HIERARCHY] values('Oblique < 90 Degree:Acute Angled Traingle',6)
insert into [TBL_TREE_HIERARCHY] values('Right Angled Triangle',6)
insert into [TBL_TREE_HIERARCHY] values('Elementary Algebra',2)
insert into [TBL_TREE_HIERARCHY] values('Abstract Algebra',2)
insert into [TBL_TREE_HIERARCHY] values('Linear Algebra',2)
insert into [TBL_TREE_HIERARCHY] values('All Sides are Equal',7)
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.