title: How to convert row to column basis with certain criteria in SQL Server?
body: <p>I have a table, each rows records are related other rows records, some rows are not related to other rows as well.</p>
  
  <p>My records are like this</p>
  
  <p><strong>Item</strong> Table</p>
  
  <pre><code>ID          ItemLookupCode       UnitOfMeasure     ParentItem
  --------------------------------------------------------------
  111         100006C0005          CRT                 0
  112         100006B0001          BAG                111       // this row is child of ID 111
  
  221         100027C0002          CRT                 0
  222         100027T0012          PCT                221
  223         100027P0001          PC                 222
  </code></pre>
  
  <p><strong>Expecting output</strong></p>
  
  <p><a href="https://i.stack.imgur.com/qdhNd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qdhNd.png" alt="ItemRelation"></a></p>
  
  <p>I tried this following query, it's working fine. Is there any other better solution for performance:</p>
  
  <pre><code>SELECT DISTINCT 
      TOP (100) PERCENT dbo.Item.ID, 
      dbo.Item.ItemLookupCode, 
      dbo.Item.UnitOfMeasure, 
      Item_1.ID AS ChildID1, 
      Item_1.ItemLookupCode AS ChildItemLookupCode1, 
      Item_1.ParentItem AS ChildParentItem1, 
      Item_1.UnitOfMeasure AS ChildUOM1, 
      Item_2.ID AS ChildID2, 
      Item_2.ItemLookupCode AS ChildItemLookupCode2, 
      Item_2.UnitOfMeasure AS ChildUOM2, 
      Item_3.ID AS ChildID3, 
      Item_3.ItemLookupCode AS ChildItemLookupCode, 
      Item_3.UnitOfMeasure AS ChildUOM3
  FROM           
      dbo.Item 
  LEFT OUTER JOIN
      dbo.Item AS Item_1 ON dbo.Item.ID = Item_1.ParentItem 
  LEFT OUTER JOIN
      dbo.Item AS Item_2 ON Item_1.ID = Item_2.ParentItem 
  LEFT OUTER JOIN
      dbo.Item AS Item_3 ON Item_2.ID = Item_3.ParentItem 
  </code></pre>