CODEWITHSUNDEEP
t-sqlsql-server-2014
SSMSJ
SSMSJ

Reputation: 219

T-SQL Display data from two rows into two columns

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

Upvotes: 1

Views: 716

Answers (2)

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Here is one option which places the min telephone number in the first column and the max telephone number, should it occur, in the second column. The count for each Ref value is used to determine whether or not a second value appears.

SELECT Ref,
       MIN(Telephone) AS Telephone1,
       CASE WHEN COUNT(*) = 1 THEN NULL ELSE MAX(Telephone) END AS Telephone2
FROM @Test
GROUP BY Ref

Upvotes: 1

Related Questions