title: T-SQL Display data from two rows into two columns
body: <p>I need to be able to put a telephone number in a second telephone number column if a row reference value repeats with different phone numbers i.e :</p>
  
  <pre><code>DECLARE @Test TABLE 
  
  (
  Ref VARCHAR(4)
  ,Telephone VARCHAR(15)
  )
  INSERT INTO @Test VALUES (1000,'02074446777')
  INSERT INTO @Test VALUES (1001,'02032968965')
  INSERT INTO @Test VALUES (1001,'07749821627')
  INSERT INTO @Test VALUES (1002,'01612448276')
  INSERT INTO @Test VALUES (1002,'07572471967')
  
  
  SELECT Ref
          ,Telephone AS Telephone1
          ,'' AS Telephone2
   FROM @Test
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Ref     Telephone1      Telephone2
  1000    02074446777 
  1001    02032968965 
  1001    07749821627 
  1002    01612448276 
  1002    07572471967 
  </code></pre>
  
  <p>But I would like to get this:</p>
  
  <pre><code>Ref     Telephone1      Telephone2
  1000    02074446777 
  1001    02032968965     07749821627
  1002    01612448276     07572471967
  </code></pre>
  
  <p>I should have mentioned I may have more than two telephone numbers, sorry :-(</p>
  
  <pre><code>DECLARE @Test TABLE 
  
  (
  Ref VARCHAR(4)
  ,Telephone VARCHAR(15)
  )
  INSERT INTO @Test VALUES (1000,'02074446777')
  INSERT INTO @Test VALUES (1001,'02032968965')
  INSERT INTO @Test VALUES (1001,'01423222888')
  INSERT INTO @Test VALUES (1001,'02079591646')
  INSERT INTO @Test VALUES (1001,'07749821627')
  INSERT INTO @Test VALUES (1002,'01612448276')
  INSERT INTO @Test VALUES (1002,'07572471967')
  
  
  SELECT Ref,
         Telephone AS Telephone1,
         '' AS Telephone2,
         '' AS Telephone3,
         '' AS Telephone4
  FROM @Test
  
  
  Ref     Telephone1      Telephone2      Telephone3  Telephone4
  1000    02074446777         
  1001    02032968965         
  1001    01423222888         
  1001    02079591646         
  1001    07749821627         
  1002    01612448276         
  1002    07572471967
  </code></pre>
  
  <p>Final Script as per </p>
  
  <p>Irawan Soetomo  :</p>
  
  <pre><code>DECLARE @Test TABLE 
  
  (
  Ref VARCHAR(4)
  ,Telephone VARCHAR(15)
  )
  INSERT INTO @Test VALUES (1000,'02074446777')
  INSERT INTO @Test VALUES (1001,'02032968965')
  INSERT INTO @Test VALUES (1001,'01423222888')
  INSERT INTO @Test VALUES (1001,'02079591646')
  INSERT INTO @Test VALUES (1001,'07749821627')
  INSERT INTO @Test VALUES (1002,'01612448276')
  INSERT INTO @Test VALUES (1002,'07572471967')
  
  
  ;
  with
  enu as
  (
      select 
          row_number() over (partition by t.Ref order by t.Telephone) as ColId,
          t.Ref,
          t.Telephone
      from 
          @Test as t
  )
  select 
      p.Ref,
      ISNULL(p.[1],'') as Telephone1,
      ISNULL(p.[2],'') as Telephone2,
      ISNULL(p.[3],'') as Telephone3,
      ISNULL(p.[4],'') as Telephone4
  from 
      enu
  pivot
  (
      max(enu.Telephone) 
      for enu.ColId in ([1],[2],[3],[4])
  ) 
  as p    
  </code></pre>
  
  <p>Accepted Result:</p>
  
  <pre><code>Ref      Telephone1    Telephone2      Telephone3       Telephone4
  1000    02074446777         
  1001    01423222888     02032968965     02079591646          07749821627
  1002    01612448276     07572471967     
  </code></pre>