title: SQL Hierarchical query - Multiple layers, only care about one
body: <p>I have a table that lists job and work order information.  Every job has at least one work order, most jobs have many work orders.  Some work orders can have sub-work orders and subs can have subs as well.  This gives us multiple layers of hierarchy.</p>
  
  <p>For a report I'm building, I want to group all work orders by the second level workorder that they fall under.  I'm calling this the SectorWO.  </p>
  
  <p>I've written the following query which almost achieves what I'm looking for, but it does not include the second level work orders themselves.  I've attempted to build sample data on SQL Fiddle but my query will not run there (it does run on production SQL Server 2012, though).  It looks like a problem with querying a temporary table after the statement that filled said table has been terminated?</p>
  
  <p>Anyways here's what I've built on SQL Fiddle, hopefully it is enough to demonstrate...</p>
  
  <p>The schema:</p>
  
  <pre><code>CREATE TABLE WOMaster (WorkOrder VARCHAR(12), ParentWorkOrder VARCHAR(12), Job VARCHAR(12))
  INSERT INTO WOMaster (WorkOrder, ParentWorkOrder, Job)
  VALUES (1,NULL,101),(2,1,101),(3,2,101),(4,3,101),(5,4,101),(6,4,101),(7,4,101),(8,4,101),(9,2,101),(10,2,101),
      (11,2,101),(12,1,101),(13,12,101),(14,12,101),(15,12,101),(16,2,101),(17,1,101),(18,17,101),(19,17,101),(20,1,101),
      (21,1,101),(22,21,101),(23,22,101),(24,22,101),(25,24,101),(26,24,101),(27,1,101),(28,27,101),(29,28,101),
      (30,1,101),(31,30,101),(32,31,101),(33,32,101),(34,1,101),(35,34,101),(36,35,101),(37,36,101);
  </code></pre>
  
  <p>The query:</p>
  
  <pre><code>    DECLARE @pJob VARCHAR(Max)
  
  SET @pJob = '101'
  
  DECLARE @pWorkOrder VARCHAR(6)
  
  SET @pWorkOrder = '1'
  
  DECLARE @WOList1 TABLE (
      Job VARCHAR(12)
      ,WorkOrder VARCHAR(12)
      ,ParentWorkOrder VARCHAR(12)
      )
  DECLARE @WOList2 TABLE (
      Job VARCHAR(12)
      ,WorkOrder VARCHAR(12)
      ,SectorWO VARCHAR(12)
      )
  
  INSERT INTO @WOList1 (
      Job
      ,WorkOrder
      ,ParentWorkOrder
      )
  SELECT Job
      ,WorkOrder
      ,ParentWorkOrder
  FROM WOMaster
  WHERE Job = @pJob
      AND ParentWorkorder &lt;&gt; WorkOrder;
  
  WITH cte
  AS (
      SELECT WO1.WorkOrder
          ,WO1.ParentWorkOrder
      FROM @WOList1 WO1
  
      UNION ALL
  
      SELECT c.WorkOrder
          ,WO1.ParentWorkOrder
      FROM @WOList1 WO1
      INNER JOIN cte c ON c.ParentWorkOrder = WO1.WorkOrder
      )
  INSERT INTO @WOList2 (
      Job
      ,WorkOrder
      ,SectorWO
      )
  SELECT WO1.Job
      ,C.WorkOrder
      ,C.ParentWorkOrder
  FROM CTE C
  LEFT JOIN @WOList1 WO1 ON C.WorkOrder = WO1.WorkOrder
  WHERE C.ParentWorkOrder IN (
          SELECT WorkOrder
          FROM @WOList1
          WHERE ParentWorkOrder = @pWorkOrder
          )
  </code></pre>
  
  <p>Expected output:</p>
  
  <pre><code>Job WorkOrder   SectorWO
  101 2   2
  101 3   2
  101 4   2
  101 5   2
  101 6   2
  101 7   2
  101 8   2
  101 9   2
  101 10  2
  101 11  2
  101 16  2
  101 12  12
  101 13  12
  101 14  12
  101 15  12
  101 17  17
  101 18  17
  101 19  17
  101 20  20
  101 21  21
  101 22  21
  101 23  21
  101 24  21
  101 25  21
  101 26  21
  101 27  27
  101 28  27
  101 29  27
  101 30  30
  101 31  30
  101 32  30
  101 33  30
  101 34  34
  101 35  34
  101 36  34
  101 37  34
  </code></pre>