将数据从两行显示到两列

Question

如果行参考值与不同的电话号码重复，则需要能够将电话号码放在第二个电话号码列中，即:

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

结果:

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

但我想得到这个:

Ref     Telephone1      Telephone2
1000    02074446777 
1001    02032968965     07749821627
1002    01612448276     07572471967

我应该提到我可能有两个以上的电话号码，对不起:-(

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

最终脚本按

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    

接受结果:

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

Answer 1

使用PIVOT，您需要编辑此脚本以支持作为列列出的电话数量。或者，让它变成动态的。

;
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

Answer 2

这里有一个选项，它将最小电话号码放在第一列，最大电话号码(如果有的话)放在第二列。每个Ref值的计数用于确定是否出现第二个值。

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