title: T-SQL: Single Table contains Permissions Tree
body: <p>I have a SQL Server Table that contains a 'Hierarchy/Tree' of User Permissions.</p>
  <p>Each Individual Permission can have values: 1 [Allowed], Blank [Not Allowed] &amp; 0 [specifically Cancelled].</p>
  <p>Each Individual Permission can be in one or more 'Permission Groups' &amp; a User can be assigned all the Individual Permissions in one or more Permission Groups.</p>
  <p>Each of the 'Permission Groups', in turn, can be in one or more higher level permission groups ... and eventually, all Permissions Groups are under a Master Group named 'Main Menu'.</p>
  <p>This SQL Code:</p>
  <pre><code>Select 
  'Main Menu' Base, 
  Description Level1,
  ParentId,
  SecurityNodesId,
  ListOrder,
  Category,
  LastModified
  
  From SecurityNodes 
  Where ParentId = 1
  Order By Description
  </code></pre>
  <p>Produces the following Output:</p>
  <p><a href="https://i.stack.imgur.com/v12Wz.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/v12Wz.jpg" alt="enter image description here" /></a></p>
  <p>'Main Menu' has a ParentId of NULL [Not Shown in screenshot].</p>
  <p>The 'Level1' 'Folders' contain other folders or Individual Permissions which are 'Referenced' by the Values under SecurityNodesId.</p>
  <p>For instance, a search for SecurityNodesId 102 [Level1 - Administration] in the ParentId column returns this list of Sub Folders under 'Level2':</p>
  <p><a href="https://i.stack.imgur.com/6MK4t.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6MK4t.jpg" alt="enter image description here" /></a></p>
  <p>So ... I can access each of these sub folders by writing separate queries.</p>
  <p>But what I want is to have an end result that displays every Node of this Permissions Tree in Table form like this:</p>
  <pre><code>Main Menu   Level1   Level2   Level3   Level4   PermissionName    PermissionValue
  </code></pre>
  <p>I have never had to do something this complex before, though I <em>have</em> done plenty of self-joins.</p>
  <p>I am currently thinking that I would need to do a self join to each self join ... to get to successive Levels of the Tree ... but I believe there may be a 'recursive' approach to this that might be more efficient?</p>
  <p>I would appreciate any help I can get with this.</p>
  <p>Thanks in advance!</p>