You are not logged in.
Announcement
#1 2010-07-22 09:41:47
- charliebarnes
- Veteran
- Registered: 2008-09-07
- Posts: 169
- Website
Location hierachy
Whats the best way to pull out the parent location hierachy of a given location in an XML report?
Offline
#2 2010-07-23 10:05:08
Re: Location hierachy
I've been meaning to try this: http://stackoverflow.com/questions/5849 … -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.
Offline
#3 2010-07-23 11:32:55
- MikeWeideli
- Veteran
- From: Littlefield Consultancy
- Registered: 2006-07-05
- Posts: 128
- Website
Re: Location hierachy
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
Mike Weideli
JNCC Recorder Approved Expert
Offline