title: SQL Server How to output one table result from multiple results with a WHILE query
body: <p>From this answer: <a href="https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor?answertab=votes#tab-top">Is there a way to loop through a table variable in TSQL without using a cursor?</a></p>
  
  <p>I'm using the method</p>
  
  <pre><code>WHILE EXISTS(SELECT * FROM #Temp)
  </code></pre>
  
  <p>The problem is that it's outputting multiple tables, if possible I'd like to output as a single table.</p>
  
  <pre><code>Declare @Id int
  
  WHILE EXISTS(SELECT * FROM #Temp)
  Begin
  
      Select Top 1 @Id = Id From #Temp
  
      --Do some processing here
  
      Delete #Temp Where Id = @Id
  
  End
  </code></pre>
  
  <p><strong>So right now it outputs this:</strong></p>
  
  <pre><code>x  y
  -- --
  1  a
  
  x  y
  -- --
  1  b
  </code></pre>
  
  <p><strong>But I'd like it to output this:</strong></p>
  
  <pre><code>x  y
  -- --
  1  a
  2  b
  </code></pre>
  
  <p><strong><em>What I'm trying to achieve</em></strong>, I have this in a field:</p>
  
  <blockquote>
    <p>1234,1432,1235</p>
  </blockquote>
  
  <p>I have a process that splits the field into records(it works with sql server 2000):</p>
  
  <pre><code>DECLARE @String VARCHAR(100)
      SELECT @String = str FROM  field --with the 1234,1432,1235
  
      SELECT SUBSTRING(',' + @String + ',', Number + 1,
      CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS str
      INTO #temp
      FROM master..spt_values
      WHERE Type = 'P'
      AND Number &lt;= LEN(',' + @String + ',') - 1
      AND SUBSTRING(',' + @String + ',', Number, 1) = ','
      GO
  </code></pre>
  
  <p>So now, #temp has:</p>
  
  <pre><code>str
  ---
  1234
  1432
  1235
  </code></pre>
  
  <p>So I need to go through each record to query the information I need.</p>
  
  <p>And I'd like it to output something like this:</p>
  
  <pre><code>str   name   age
  ---   ----   ---
  1234  Bob    23
  1432  Jay    41
  1235  Tim    12
  </code></pre>
  
  <p>The current While loop outputs it like this, which I <strong>don't</strong> want:</p>
  
  <pre><code>str   name   age
  ---   ----   ---
  1234  Bob    23
  
  str   name   age
  ---   ----   ---
  1432  Jay    41
  
  str   name   age
  ---   ----   ---
  1235  Tim    12
  </code></pre>
  
  <p><strong><em>Final Working Result:</em></strong></p>
  
  <pre><code>SET NOCOUNT ON;
  
  DECLARE @String VARCHAR(1000);
  SELECT @String = Tnn FROM (SELECT 
   CO.USER_2 AS Tnn
  FROM 
      [VMFG].[dbo].[CUSTOMER_ORDER] AS CO 
      LEFT JOIN DBO.Tnn_Header AS Tnn ON Tnn.TnnNumber = CO.USER_2 AND Tnn.StatusID = '5' WHERE CO.ID = 'ORDERID') AS Place --with the 1234,1432,1235
  
  DECLARE @Id nvarchar(50),
          @Discount nvarchar(50), 
          @Spin nvarchar(50), 
          @Commission_Hmm nvarchar(50), 
          @Commission nvarchar(50), 
          @TnnID nvarchar(50);
  
  DECLARE @Output TABLE (
  TnnNumber nvarchar(50),
          Discount nvarchar(50), 
          Spin nvarchar(50), 
          Commission_Hmm nvarchar(50), 
          Commission nvarchar(50), 
          TnnID nvarchar(50));
  
  DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
  FOR  SELECT SUBSTRING(',' + @String + ',', Number + 1,
       CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1) AS [ID]
       FROM master..spt_values
       WHERE Type = 'P'
       AND Number &lt;= LEN(',' + @String + ',') - 1
       AND SUBSTRING(',' + @String + ',', Number, 1) = ',';
  
  OPEN crs;
  
  FETCH NEXT
  FROM  crs
  INTO  @Id;
  
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
      -- do some processing..
  SELECT 
  @Id = TH.TnnNumber,
  @Discount = CASE WHEN COUNT(DISTINCT TL.DiscountCodeID) &gt; 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(DC.Value) AS VARCHAR(60)) END,
  @Spin = CASE WHEN TS.SpinID &gt; 4 THEN 'Has Specifics, View Tnn' ELSE TS.Value END,
  @Commission_Hmm = CASE WHEN COUNT(DISTINCT TL.Commission_Hmm) &gt; 1 THEN 'Varies, View Tnn' ELSE CAST(MAX( ISNULL(str(TL.Commission_Hmm,12),'Default Comm')) AS VARCHAR(60)) END,
  @Commission = CASE WHEN COUNT(DISTINCT TL.Commission) &gt; 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission,12),'Default Comm')) AS VARCHAR(60)) END,
  @TnnID = TL.TnnID 
  
  FROM DBO.Tnn_Header AS TH
  LEFT JOIN DBO.Tnn_LINE AS TL ON TH.TnnID = TL.TnnID
  LEFT JOIN DBO.Tnn_Spin AS TS ON TH.SpinID = TS.SpinID
  LEFT JOIN DBO.Tnn_DiscountCode AS DC ON TL.DiscountCodeID = DC.DiscountCodeID 
  
  WHERE TnnNumber = @id
  
  GROUP BY 
  TH.TnnNumber,
  TS.SpinID,
  TS.Value,
  TL.TnnID
  -- end do some processing..
      INSERT INTO @Output (TnnNumber, Discount, Spin, Commission_Hmm, Commission, TnnID)
      VALUES (@Id, @Discount, @Spin, @Commission_Hmm, @Commission, @TnnID);
  
      FETCH NEXT
      FROM  crs
      INTO  @Id;
  END;
  
  CLOSE crs;
  DEALLOCATE crs;
  
  SELECT TnnNumber, Discount, Spin, Commission_Hmm, Commission, TnnID
  FROM   @Output;
  </code></pre>