SQL How to merge 3 while statement results into one result
<p>SQL Server management Studio -</p>

<p>How do I union during a while loop ?</p>

<p>ParentID is a Inner-relation towards ChildID</p>

<pre><code>WHILE EXISTS (SELECT ParentID FROM Table1)
    BEGIN 
        SELECT * FROM FinalTable WHERE FinalTableID = (SELECT c.ChildID FROM Table1 as c WHERE c.ChildID = (SELECT d.ChildID FROM Table1 as d WHERE d.ChildID = @MyCounter))
      IF(SELECT e.ParentID FROM Table1 as e WHERE e.ChildID = @MyCounter) IS NULL
        BREAK
      ELSE
      SET @MyCounter = (SELECT d.ParentID FROM Table1 as d WHERE d.ChildID = @MyCounter)
        CONTINUE
    END
</code></pre>

<p>This code currently results in multiple row collections with containing different results, is it possible to merge everything into a complete result?</p>

<pre><code>FinalTable 
-------------------------------
|     ID  Name  Text  ChildID
|  1  1   Foo1  Bar1  1
|  2  2   oof1  Bra1  1
|  3  3   ofo1  Rab1  1
--------------------------------
</code></pre>

<hr>

<pre><code>FinalTable 
--------------------
|     ID  Row2  Row3  ChildID
|  1  8   Foo2  Bar2  2
|  2  9   oof2  Bra2  2
|  3  10  ofo2  Rab2  2
--------------------
</code></pre>

<hr>

<pre><code>FinalTable 
--------------------
|     ID  Name  Text  ChildID
|  1  14  Foo3  Bar3  3
|  2  17  oof3  Bra3  3
|  3  19  ofo3  Rab3  3
--------------------
</code></pre>

<p>Wanted Result :</p>

<pre><code>FinalTable 
--------------------
|     ID  Name  Text  ChildID
|  1  1   Foo1  Bar1  1
|  2  2   oof1  Bra1  1
|  3  3   ofo1  Rab1  1
|  4  8   Foo2  Bar2  2
|  5  9   oof2  Bra2  2
|  6  10  ofo2  Rab2  2
|  7  14  Foo3  Bar3  3
|  8  17  oof3  Bra3  3
|  9  19  ofo3  Rab3  3
--------------------
</code></pre>