title: SQL recursive get BOM from PSP
body: <p>I am having a MS SQL Server (2016) and a database which contains i.a. table like this : (it´s a view created in an Autodesk PSP Database - please don´t ask why ... :-) )</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CHILD_AIMKEY</th>
  <th>QUANTITY</th>
  <th>PARENT_AIMKEY</th>
  <th>StatusOfParent</th>
  <th>StatusOfChild</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>5706657</td>
  <td>1</td>
  <td>5664344</td>
  <td>100</td>
  <td>103</td>
  </tr>
  <tr>
  <td>5706745</td>
  <td>1</td>
  <td>5664344</td>
  <td>100</td>
  <td>103</td>
  </tr>
  <tr>
  <td>5707104</td>
  <td>1</td>
  <td>5664344</td>
  <td>100</td>
  <td>103</td>
  </tr>
  <tr>
  <td>5707109</td>
  <td>1</td>
  <td>5664344</td>
  <td>100</td>
  <td>100</td>
  </tr>
  <tr>
  <td>5801062</td>
  <td>1</td>
  <td>5664344</td>
  <td>100</td>
  <td>103</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The &quot;children&quot; can contain other &quot;children&quot; and in that case they would be their &quot;parents&quot;.</p>
  <p>So it´s a standard structured BOM table from a CAD PDM System.</p>
  <p>If I do the following &quot;Select Statement&quot; I get all the children of the top level parent:</p>
  <pre><code>SELECT [CHILD_AIMKEY] , [POSITION], [QUANTITY] ,[PARENT_AIMKEY],[StatusOfParent],[StatusChild] FROM database_table where Parent_aimkey = '5664344'
  </code></pre>
  <p>(as shown in the table above)</p>
  <p>My first question is : How to recursivly process all children of each parent from that table ? (Could be an other table or direct output)
  The format should be: Parent_Aimkey, Child_Aimkey, Quantity</p>
  <p>The second question is a bit more complicated:</p>
  <p>I try it with some &quot;pseudo code&quot;:</p>
  <pre><code>If Tree_Level_of_DIRECT_Parent &lt; 3 then show CHILD_AIMKEY,QUANTITY in queryresult_above
  If Tree_Level_of_DIRECT_Parent &gt; 2 and StatusOf_DIRECT_Parent = 103 and StatusOf_DIRECT_Child = 103 then show CHILD_AIMKEY,QUANTITY in queryresult_above
  </code></pre>
  <p>Is that in some way possible ? (If there is a need to extend the database view of an other field or another table, that´s no problem)</p>
  <p>I know this looks a bit confusing, but what I need is the Autodesk Inventor structured BOM in an SQL Statement or stored procedure.</p>
  <p>Any would be really much appreciated</p>
  <p>Thanks</p>
  <p>Alex.</p>