Self Join - Show all locations that are not a parent to another location and show their parent.

SELECT L.LOCATION,L.PARENTID,P.LOCATION as PARENTLOC
FROM LOCATION L INNER JOIN LOCATION  P ON L.PARENTID=P.LOCID
WHERE L.LOCID NOT IN (SELECT DISTINCT PARENTID FROM LOCATION WHERE PARENTID IS NOT NULL)

Recusive Join to show location path.

WITH CTE (LOCID, LOCATION,RECORDER) AS
(
    SELECT LOCID, cast(location as varchar(max)) as locpath,RECORDER
    FROM Location g
    WHERE parentid IS NULL
    UNION ALL
    SELECT g2.LOCID, Cast(CTE.location + '\' + g2.Location as Varchar(max)),g2.RECORDER
    FROM Location g2
    JOIN CTE ON g2.PARENTID = cte.LocId
)
SELECT   * FROM   CTE
ORDER BY RECORDER