根据表中的列创建新列

乐声

我在下面的示例中有代码。

    legacy_id phone_type phone_number
         1        f      1234567890
         1        b      1233854100
         1        f      4110256565
         2        f      0707070770
         2        b      7895120044

我希望数据最终如下所示。

    legacy_id  f_phone_number_1 b_phone_number_1 f_phone_number_2
         1      1234567890        1233854100        4110256565
         2      0707070770        7895120044

我最初的方法可行，但是我希望这样做的效率更高。

    Select fill.legacy_id, max(fill.f_phone_number_1),max(fill.b_phone_number_1),max(fill.f_phone_number_2)
    from
    (
      Select
           a.legacy_id as legacy_id, a.phone_type as phone_type,
           case
               when a.phone_type = 'F' then a.phone_number and 
               dense_rank() over (partition by a.legacy_id, a.phone_type order by a.legacy_id, a.phone_type, a.phone_number) = 1
               else null
           end as f_phone_number_1,
           case
               when a.phone_type = 'F' then a.phone_number and
               dense_rank() over (partition by a.legacy_id, a.phone_type order by a.legacy_id, a.phone_type, a.phone_number) = 2
               else null
           end as f_phone_number_2,
           case
               when a.phone_type = 'b' then a.phone_number and
               dense_rank() over (partition by a.legacy_id, a.phone_type order by a.legacy_id, a.phone_type, a.phone_number) = 1
               else null
           end as b_phone_number_1
      from table a
      group by a.legacy_id, a.phone_type, a.phone_number
    ) fill
    group by fill.legacy_id

有没有更有效的方法来解决这个问题？

约翰·卡佩莱蒂

如果您不需要动态处理，则条件聚合应该可以解决问题

Declare @YourTable table (legacy_id int,phone_type varchar(25),phone_number varchar(25))
Insert Into @YourTable values
(1,'f','1234567890'),
(1,'b','1233854100'),
(1,'f','4110256565'),
(2,'f','0707070770'),
(2,'b','7895120044')

Select legacy_id
      ,f_phone_number_1 = max(case when phone_type='f' and RowNr=1 Then phone_number else '' end)
      ,b_phone_number_1 = max(case when phone_type='b' and RowNr=1 Then phone_number else '' end)
      ,f_phone_number_2 = max(case when phone_type='f' and RowNr=2 Then phone_number else '' end)
      ,b_phone_number_2 = max(case when phone_type='b' and RowNr=2 Then phone_number else '' end)
 From (
        Select *
              ,RowNr=Row_Number() over (Partition By legacy_id,phone_type Order By (Select NULL) ) 
         From  @YourTable 
      ) A
 Group By legacy_id

退货

legacy_id   f_phone_number_1    b_phone_number_1    f_phone_number_2    b_phone_number_2
1           4110256565          1233854100          1234567890  
2           0707070770          7895120044

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。