title: Recursively calculate path for each item of the self referencial hierarchial table
body: <p>I have a table like this (SQL Server 2008):</p>
  
  <pre><code>[ATTACHMENTS].[ID]
  [ATTACHMENTS].[Name]
  [ATTACHMENTS].[DocumentID],
  [ATTACHMENTS].[ParentAttachmentID]
  </code></pre>
  
  <p>If <code>[ParentAttachmentID]</code> is <code>NULL</code> or <code>0</code> - then the record is top. Otherwise - the record is a child. Every child can be (and can't be) the parent of some other child elementS. </p>
  
  <p>I need to calculate "path" for every record of the table. Path is:</p>
  
  <p><code>ParentAttachment.Path + ' &gt; ' + Attachment.Path</code> (ParentAttachment.Path is recursive)</p>
  
  <p>I try something like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH attachments AS (
   SELECT *,
       [ATTACHMENTS].[Name] AS Path
       FROM [ATTACHMENTS]
       WHERE  [ATTACHMENTS].[ParentAttachmentID] IS NULL
              OR [ATTACHMENTS].[ParentAttachmentID] = 0
   UNION ALL
   SELECT a.*,
       c.Path + ' &gt; ' + a.[Name]
       FROM [ATTACHMENTS] a
           INNER JOIN attachments c
               ON a.[ParentAttachmentID] = c.[ID]
  )
  </code></pre>
  
  <p>But it doesn't work properly (Path is invalid for some elements due to duplicates as I think). Where I made a mistake? Please, help me to fix this problem.</p>
  
  <p><strong>UPD 2</strong>: CSV with data from table [ATTACHMENTS] - <a href="http://pastebin.com/WMd6HJ7j" rel="nofollow">http://pastebin.com/WMd6HJ7j</a>
  CSV with result of recursive query:  <a href="http://pastebin.com/7pqs0dx1" rel="nofollow">http://pastebin.com/7pqs0dx1</a></p>
  
  <p>Thanx!</p>