title: TSQL select into insert multiple rows into 1 row, many columns simplest way
body: <p>I'm using MS SQL Server 2008 R2 and I have a results table that I wish to populate from data belonging from another table.</p>
  
  <pre><code>// Results table definition
  
  create table resultsTable
  (
    RowId int Identity(1,1) Not Null,
  
    sid1 int, colA1 int, colB1 int, colC1 int,
    sid2 int, colA2 int, colB2 int, colC2 int,
    sid3 int, colA3 int, colB3 int, colC3 int,
    colX  bit      
  )
  
  // Source table definition
  
  create table sourceTable
  (
    RowId int Identity(1,1) Not Null ,
    colA int , colB int , colC int , colX bit ,
  ) 
  </code></pre>
  
  <p>If the source table had 100+ rows of data, I would like to be able to select 3 rows using something like</p>
  
  <pre><code>select top 3
         colA,
         colB,
         colC,
         colX
  from sourceTable
  where Rowid &gt;= @RowIdVariable
  </code></pre>
  
  <p>where @RowIdVariable value is coming from another table, RowsIdFrom</p>
  
  <pre><code>create table RowIdsFromTable
  ( 
    id int Identity(1,1) Not Null,
    RowIdFrom int
  )
  </code></pre>
  
  <p>I am thinking of using a cursor for this.</p>
  
  <pre><code>declare MyCursor cursor for
      select RowIdFrom from RowIdsFromTable order by RowIdFrom asc
  Open MyCursor 
  </code></pre>
  
  <p>I know my results will look something like this below for the select statement</p>
  
  <pre><code>sid | colA | colB | colC | colX
  -------------------------
  1     1      2      3      0
  2     4      5      6      0
  3     7      8      9      1 
  </code></pre>
  
  <p>With these results, I would very much like to know how I can could insert them into my resultsTable so that</p>
  
  <pre><code>select *
  from resultsTable
  </code></pre>
  
  <p>should look like the results below and do it in the most efficient way please :D</p>
  
  <pre><code>rowId | sid1 | colA1 | colB1 | colC1 | sid2 | colA2 | colB2 | colC2 | sid3 | colA3 | colB3 | colC3 | colX
  1       1       1      2       3       2      4       5       6       3      7       8       9       1
  </code></pre>
  
  <p>where colX bit value belongs to the last row, as in the above example, </p>
  
  <pre><code>colA | colB | colC | colX
  -------------------------
  7      8      9      1 
  </code></pre>
  
  <p>Thanks for any pointers and help :D</p>
  
  <p>140213 Edit:</p>
  
  <p>To give more information or to be more clear, I am needing to insert into the resultsTable the returned row because I have another table that defines all the RowId that I should be selecting from.  This table just contains the starting point RowId's.</p>
  
  <p>The desired output may therefore look like below if RowIdsFromTable contained 3 values, 1, 12 and 20</p>
  
  <pre><code>rowId | sid1 | colA1 | colB1 | colC1 | sid2 | colA2 | colB2 | colC2 | sid3 | colA3 | colB3 | colC3 | colX
  1       1      1       2       3       2      4       5       6       3      7       8       9       1
  2       12     24      9       13      32     13      43      88      14     2       54      23      0
  3       20     xx      xx      xx      21     xx      xx      xx      22     xx      xx      xx      1
  </code></pre>
  
  <p>where "xx" represents the integer values from the sourceTable.  Please note the order of the sid's ascending.</p>
  
  <p>I attach an sql fiddle with the tables and some test data: <a href="http://sqlfiddle.com/#!3/95fa8/1/0" rel="nofollow">http://sqlfiddle.com/#!3/95fa8/1/0</a>.</p>