title: SQL Server Management Studio using While loop function to split result
body: <p>I have a task to find a way to use <code>WHILE</code> loop function to try to split a single selected result into many, the table is simple column in (var) format looks like : </p>
  
  <pre><code>SELECT [Names ID] 
  FROM [Names Database]
  </code></pre>
  
  <p>The result is just a column with numbers some of which are repeated many times. My question is there a way to use <code>WHILE</code> function to split the result grouped by those <code>[Names ID]</code> numbers so it looks like I used <code>SELECT</code> and <code>WHERE</code> filter for each different number?</p>
  
  <p>i used this : </p>
  
  <pre><code>SELECT CUSTOMER_ID
        ,DENSE_RANK() OVER(ORDER BY CUSTOMER_ID) as [number]
  INTO [ID NUMBERS]
  FROM [Customer]
  
  USE [TEST];
  GO
  DECLARE @N int = 0
  WHILE (SELECT max(NUMBER) FROM [ID NUMBERS] ) &gt; @N 
  BEGIN
  SET @N = @N + 1
  SELECT CUSTOMER_ID,number FROM [ID NUMBERS]
  END
  </code></pre>
  
  <p>i used the dense_rank basically not to calculate the long customer_id +1 the result i get looks like : </p>
  
  <p><a href="https://i.stack.imgur.com/EfgKT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EfgKT.png" alt="enter image description here"></a></p>
  
  <p>how can i fix it too look like this : </p>
  
  <p><a href="https://i.stack.imgur.com/zYtm0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zYtm0.png" alt="enter image description here"></a></p>
  
  <p>manage to get the result i want by using this : </p>
  
  <p>SELECT distinct CUSTOMER_ID
        ,DENSE_RANK() OVER(ORDER BY CUSTOMER_ID) as [number] INTO [TEST].[Trainee].[ID NUMBERS] FROM [TEST]  USE [TEST]; GO DECLARE @N BIG INT = 1  WHILE (SELECT max(NUMBER) FROM [TEST][ID NUMBERS] ) >= @N  BEGIN
            SELECT  @N
               ,ID.[Customer_ID]
               ,[number]<br>
           FROM [TEST].[ID NUMBERS] AS ID
          WHERE @N = NUMBER    SET @N = @N + 1 END</p>