Hierarchical SQL table and finding a record's lineage
Posted on 2010-11-29
I have a hierarchical SQL table mimicking a directory tree structure, with fields like the following:
where a record's ParentID is defined as the ID of the record's intended parent record.
When I preform a Select, I would like to add a column, named "Path", that contains a string representation of an item's lineage, (Eg \GrandParent\Parent\Me). Given the ID of any item within the table, how do I design a select command to recursively build a lineage string? I use the word "recursion" loosely -- I know that actual recursion is a resource hog. Does anyone have a workable strategy for this?
SELECT ID, ParentID, ItemName (...something...) as Path