title: SQL: How to create view from a recursive query?
body: <p>Question: I have a view which I want to derive from a recursive query.</p>
  
  <p>The query is of the same structure as this one here:
  <a href="http://forums.asp.net/t/1207101.aspx" rel="noreferrer">http://forums.asp.net/t/1207101.aspx</a></p>
  
  <p>And represents a treeview as an ordered dataset.</p>
  
  <p>How can I create a view which does this:</p>
  
  <pre><code>;WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS
  (
      SELECT ID, [NAME], PARENT_ID, 0 AS Depth, CONVERT(varchar(255), [Name]) AS Sort FROM Category
      WHERE PARENT_ID = 0
      UNION ALL
      SELECT CT.ID, CT.[NAME], CT.PARENT_ID, Parent.Depth + 1 AS Depth, 
      CONVERT(varchar(255), Parent.Sort + ' | ' + CT.[NAME]) AS Sort
      FROM Category CT
      INNER JOIN Tree as Parent ON Parent.ID = CT.PARENT_ID
  )
  
  -- HERE IS YOUR TREE, Depths gives you the level starting with 0 and Sort is the Name based path
  SELECT ID, [NAME], PARENT_ID, Depth, Sort FROM Tree
  ORDER BY Sort
  </code></pre>