|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → How Do I... & Other Questions → Location hierachy
Whats the best way to pull out the parent location hierachy of a given location in an XML report?
I've been meaning to try this: http://stackoverflow.com/questions/584904/cte-to-traverse-back-up-a-hierarchy and this: http://msdn.microsoft.com/en-us/library/ms186243.aspx
You can only use Common Table Expressions (CTEs) if you're on SQL 2005 or later. If you've not come across them before, here's an intro. They're ideal for this sort of recursive task.
Depends on what you want to do. The following returns a comma separated string of the parents names for locations into a tempoary table. The resulting table could then be used in a main query, It would need adapting to meet specific needs.
INSERT INTO #Parents(Location_Key, Parent_key)
SELECT L.Location_Key, L.Parent_key
FROM
LOCATION L
WHERE L.Parent_key is not null
WHILE 1=1 BEGIN
UPDATE #Parents
SET Parents = LN.Item_name + ', ' + isnull(P.Parents,''), Parent_Key = L.Parent_Key
FROM #Parents P
INNER JOIN Location L ON L.Location_key = P.Parent_Key
INNER JOIN Location_Name LN ON LN.Location_Key = L.Location_Key AND LN.Preferred = 1
IF @@ROWCOUNT=0
BREAK
END
DROP Table #parents
Mike
Forum → How Do I... & Other Questions → Location hierachy
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.