 <p>This creates your typical hierarchical table and uses a CTE to select the hierarchy structure and create a path for each item.</p> <pre><code>CREATE TABLE tblHierarchy (ID int, ParentID int NULL, Name varchar(128)); INSERT INTO tblHierarchy VALUES (1, NULL, '1'); INSERT INTO tblHierarchy VALUES (2, NULL, '2'); INSERT INTO tblHierarchy VALUES (3, NULL, '3'); INSERT INTO tblHierarchy VALUES (4, 1, '1.1'); INSERT INTO tblHierarchy VALUES (5, 1, '1.2'); INSERT INTO tblHierarchy VALUES (6, 4, '1.1.1'); WITH Parent AS ( SELECT ID, ParentID, Name AS Path FROM tblHierarchy WHERE ParentID IS NULL UNION ALL SELECT TH.ID, TH.ParentID, CONVERT(varchar(128), Parent.Path + '/' + TH.Name) AS Path FROM tblHierarchy TH INNER JOIN Parent ON Parent.ID = TH.ParentID ) SELECT * FROM Parent </code></pre> <p>OUTPUT:</p> <pre><code>ID ParentID Path 1 NULL 1 2 NULL 2 3 NULL 3 4 1 1/1.1 5 1 1/1.2 6 4 1/1.1/1.1.1 </code></pre>