- & gt; Generate test data table: [TB] 

IF OBJECT_ID (' [TB]) IS NOT NULL 
DROP TABLE (TB), 

GO 

The CREATE TABLE/TB 
(
[ID] (INT), 
[Name] [NVARCHAR] (10), 
[PID] [INT] 
); 

INSERT INTO [TB] 
SELECT '1', 
'A', 
'0' 
UNION ALL 
SELECT '2', 
'B', 
'0' 
UNION ALL 
SELECT '3', 
'A1', 
'1' 
UNION ALL 
SELECT the '4', 
'B1, 
'2' 
UNION ALL 
SELECT the '5', 
'B2, 
'2' 
UNION ALL 
SELECT the '6', 
'A11, 
'3' 
UNION ALL 
SELECT the '7', 
'A12, 
'3' 
UNION ALL 
SELECT the '8', 
'A111, 
'6' 
UNION ALL 
SELECT the '9', 
'A112, 
'6' 
UNION ALL 
SELECT the '10', 
'A1111' 
'8' 
UNION ALL 
SELECT '11', 
'A1112' 
'8'. 



- SELECT * FROM [TB] 



- & gt; SQL queries are as follows: 

- 1. BOM explosion and depth of the node by the query methods: 

-- 1.1 SQL2000 specific node, and by the depth of nodes sorting: 

IF OBJECT_ID (' dbo. F_GetTree) IS NOT NULL 
The DROP FUNCTION dbo. F_GetTree; 

GO 

The CREATE FUNCTION dbo. F_GetTree (@ ID AS INT=NULL) 
RETURNS the @ r TABLE 
(
ID INT, 
LVL INT, 
Px VARBINARY (8000) 
) 
AS 
The BEGIN 

DECLARE @ LVL INT. 

The SET @ LVL=0; 



IF ISNULL (@ ID, 0) & lt;> 0 
INSERT the @ r 
VALUES (@ ID, @ LVL, CAST (@ ID AS VARBINARY)); 

The ELSE 
INSERT the @ r 
SELECT ID, 
@ LVL, 
CAST AS VARBINARY (ID) 
The FROM TB 
WHERE PID=0; 



WHILE @ @ rowcount & gt; 0 
The BEGIN 

The SET @ LVL=@ LVL + 1; 

INSERT the @ r 
The SELECT, Anderson D 
@ LVL, 
P. x + CAST (Anderson, D AS VARBINARY) 
The FROM TB a 
The JOIN @ r b ON Amy polumbo ID=b.I D 
AND b.l vl=@ LVL 1; 

END; 

RETURN; 

END; 

GO 

- show all nodes: 

SELECT a. *, 
B.l vl 
The FROM TB a 
The JOIN dbo. F_GetTree (1) b ON Anderson, D=b.I D 
The ORDER BY p. x.