I went through several options on making Location Hierarchy Reports, and found these queries to be quite beneficial. The code is different, depending on the backend being Oracle or Microsoft.
Oracle
select level, lpad (‘ ‘, 8 * (level – 1)) || lh.location “LOCATION”, l.description, lh.parent, lh.siteid
from lochierarchy lh, locations l
where lh.location = l.location and lh.siteid = l.siteid and lh.siteid = ‘TXPANHANDLE’
start with lh.location =’CANADIAN’
connect by prior lh.location = lh.parent
Microsoft
select lo.location, lh.parent, lo.description
into #tempo
from locations lo
inner join lochierarchy lh on lh.location = lo.location
where lo.status in (‘OPERATING’, ‘ACTIVE’) and lh.parent not in (‘DELETE’);
WITH Hierarchy
AS ( SELECT parent AS RootID ,
1 AS [Level] ,
‘\\’ + CAST(parent AS VARCHAR(MAX)) + ‘\’ + CAST(location AS VARCHAR(MAX))AS [Path] ,
parent ,
location
FROM #tempo
WHERE NOT parent IN ( SELECT location
FROM #tempo )
UNION ALL
SELECT P.RootID ,
P.[Level] + 1 ,
P.[Path] + ‘\’ + CAST(C.location AS VARCHAR(MAX)) ,
C.parent ,
C.location
FROM Hierarchy P
INNER JOIN #tempo C ON P.location = C.parent
)
SELECT case level
when 1 then ”
when 2 then ‘ |–‘
when 3 then ‘ | |–‘
when 4 then ‘ | | |–‘
when 5 then ‘ | | | |–‘
when 6 then ‘ | | | | |–‘
when 7 then ‘ | | | | | |–‘
when 8 then ‘ | | | | | | |–‘
when 9 then ‘ | | | | | | | |–‘
when 10 then ‘ | | | | | | | | |–‘
else ‘XXX’
end
+ location ‘Location Hierarchy’
FROM Hierarchy
ORDER BY [Path] ;
drop table #tempo;