title: Transform hierarchical data into flat table
body: <p>I am researching about hierarchical data (as a solution to <strong>generalize work orders</strong>) by using ms sql.</p>
  
  <p>*</p>
  
  <blockquote>
    <p>I am open to design change on my initial table and/or to add other
    tables.</p>
  </blockquote>
  
  <p>*</p>
  
  <p>Here is my data</p>
  
  <pre><code>ID  ParentID    Type        Value
  38  0           Num         327
  39  38          Sector      21
  40  38          Sector      22
  43  40          Product     NS
  44  40          Product     MS
  50  40          Temp        RAS
  48  44          Quantity    60
  47  43          Quantity    25
  41  39          Product     ARF
  42  39          Product     BRF
  49  39          Temp        RAS
  51  39          Cible       Acarien A.
  46  42          Quantity    30
  52  42          Cible       Acarien B.
  45  41          Quantity    20
  </code></pre>
  
  <p>I would like to transform it into :</p>
  
  <pre><code>Num Sector  Product Quantity
  327 21      ARF     20
  327 21      BRF     30
  327 22      NS      25
  327 22      MS      60
  </code></pre>
  
  <p>[Result using Gurwinder's answear]</p>
  
  <pre><code>num sector  product    quantity
  327 22      MS         60
  327 22      NS         25
  327 21      BRF        30
  327 21      BRF        Acarien B.
  327 21      ARF        20
  </code></pre>
  
  <p>[Shungo's approach]</p>
  
  <pre><code>&lt;root&gt;
    &lt;row Num="327" Sector="s2" Temp="normal" /&gt;
    &lt;row Num="327" Sector="s2" Product="BRF" Qte="70" /&gt;
    &lt;row Num="327" Sector="s2" Product="ARF" Qte="45" /&gt;
    &lt;row Num="327" Sector="s1" Temp="normal" /&gt;
    &lt;row Num="327" Sector="s1" Cible="Acarien a." /&gt;
    &lt;row Num="327" Sector="s1" Product="NS" Qte="35" /&gt;
    &lt;row Num="327" Sector="s1" Product="NS" Cible="Acarien b." /&gt;
    &lt;row Num="327" Sector="s1" Product="MS" Qte="60" /&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p><strong><em>Thank's a lot for your time guys.</em></strong></p>