Hierarchy Structure SQL Query

-- DROP TABLE #tblAsset;
CREATE TABLE #tblAsset(ID int IDENTITY(1,1),AssetID bigint,ParentID bigint,Asset varchar(90),Node int);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node)VALUES(3319,3214,'Apartment',1);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3320,3319,'Building 1508',2);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3321,3319,'Building 1509',2);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3322,3319,'Building 1510',2);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES(3323,3320,'Room 210',3);
INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3324,3320,'Room 211',3);

WITH
cteBuildPath AS
(--==== This is the "anchor" part of the recursive CTE
SELECT anchor.AssetID,
anchor.ParentID,
anchor.Asset,
anchor.Node,
1 AS HLevel,
CAST(CAST(anchor.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM #tblAsset AS anchor
WHERE Node = 1
UNION ALL -------------------------------------------------------------------
--==== This is the "recursive" part of the CTE that adds 1 for each level
-- and concatenates each level of AssetID's to the SortPath column.
SELECT recur.AssetID,
recur.ParentID,
recur.Asset,
recur.Node,
cte.HLevel + 1 AS HLevel,
CAST(cte.SortPath + CAST(Recur.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM #tblAsset AS recur
INNER JOIN cteBuildPath AS cte
ON cte.AssetID = recur.ParentID
)--==== This final INSERT/SELECT creates the "narrow surrogate" column (NodeNumber)
SELECT AssetID, ParentID, Asset = SPACE((Hlevel-1)*4)+Asset, Node --,SortPath
FROM cteBuildPath
ORDER BY SortPath
;

AssetID ParentID Asset Node
-------------------- -------------------- ------------------------------ -----------
3319 3214 Apartment 1
3320 3319 Building 1508 2
3323 3320 Room 210 3
3324 3320 Room 211 3
3321 3319 Building 1509 2
3322 3319 Building 1510 2

Jeff Moden (5/24/2012)

dwain.c (5/24/2012)

Jeff Moden (10/24/2010)

For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.

Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.

Only by a year and eight months. Smile

(Just having a little fun here).

Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.