T-SQL Display data from two rows into two columns

Question

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 :

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

Result:

Ref     Telephone1      Telephone2
1000    02074446777 
1001    02032968965 
1001    07749821627 
1002    01612448276 
1002    07572471967 

But I would like to get this:

Ref     Telephone1      Telephone2
1000    02074446777 
1001    02032968965     07749821627
1002    01612448276     07572471967

I should have mentioned I may have more than two telephone numbers, sorry :-(

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

Final Script as per

Irawan Soetomo :

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    

Accepted Result:

Ref      Telephone1    Telephone2      Telephone3       Telephone4
1000    02074446777         
1001    01423222888     02032968965     02079591646          07749821627
1002    01612448276     07572471967     

Answer 1

Using PIVOT, you will need to edit this script to support the number of Telephones to list as columns. Or, just make it dynamic.

;
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,
    p.[1] as Telephone1,
    p.[2] as Telephone2
from 
    enu
pivot
(
    max(enu.Telephone) 
    for enu.ColId in ([1],[2])
) 
as p