SQL Hierarchy

[Pho]

Hi All,

I'd like to parse a hierarchy in SQL. The table structure is set-up with an id/parent system, nothing too advanced.

I want to return the data in a depth-first style list, as a table (i.e. to fully expand all of 3.1 before moving onto 3.2):

A CTE (Common Table Expression) approach gives me this, which is the correct data but in the wrong order:

ID          Parent      Name       Level
----------- ----------- ---------- -----------
3           NULL        3          0
5           3           3.1        1
6           3           3.2        1
8           3           3.3        1
10          8           3.3.1      2
12          8           3.3.2      2
7           10          3.3.1.1    3
11          6           3.2.1      2
4           11          3.2.1.1    3

But I want this:

ID          Parent      Name       Level
----------- ----------- ---------- -----------
3           NULL        3          0
5           3           3.1        1
6           3           3.2        1
11          6           3.2.1      2
4           11          3.2.1.1    3
8           3           3.3        1
10          8           3.3.1      2
7           10          3.3.1.1    3
12          8           3.3.2      2

Unfortunately I can't sort by the name field in the real world because that's set to other things.

This should work straight off in SQL Server and produce the above result:

-- Table structure
DECLARE @test table (
	ID INT PRIMARY KEY,
	Parent INT NULL,
	Name NVARCHAR(100)
);

-- Sample data
INSERT @test (ID, Parent, Name)
SELECT 1, NULL, '1' UNION ALL
SELECT 2, NULL, '2' UNION ALL
SELECT 3, NULL, '3' UNION ALL
SELECT 4, 11, '3.2.1.1' UNION ALL
SELECT 5, 3, '3.1' UNION ALL
SELECT 6, 3, '3.2' UNION ALL
SELECT 7, 10, '3.3.1.1' UNION ALL
SELECT 8, 3, '3.3' UNION ALL
SELECT 9, 2, '2.1' UNION ALL
SELECT 10, 8, '3.3.1' UNION ALL
SELECT 11, 6, '3.2.1' UNION ALL
SELECT 12, 8, '3.3.2';



-- CTE Version for ID 3
WITH Hierarchy (ID, Parent, Name, [Level]) AS
(
	SELECT ID, Parent, Name, 0
	FROM  @test
	WHERE ID = 3
	
	UNION ALL
	
	SELECT t.ID, t.Parent, t.Name, level+1
	FROM @test t
	INNER JOIN Hierarchy h
		ON t.Parent = h.ID
)

SELECT * FROM Hierarchy

-- Insert amazing fix here :p

Any ideas? I've been thinking about it for a while now and no doubt am missing something really simple. I keep envisaging lots of horrid recursive functions. :/

I'm using SQL Server 2008. I know it has HierarchyID but I think it would be too much work/hassle to convert the current system to it.

Cheers.

 

[aln]

Can you expand on the reason that you cannot use ORDER BY name?

 

[Pho]

Because name is set to company names, or individuals names. It has no bearing on the hierarchy at all (I just typed it above to explain things easier).

I.e.:
-Organisation
--Company A
---Person A
---Person B
--Company B
---Person A
---Person B

Sorting by name would give something like this:
Company A
Company B
Organisation
Person A
Person A
Person B
Person B