Create new columns depending on the columns in a table I have code in the this example:


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



I want the data to end up like the following:


legacy_id  f_phone_number_1 b_phone_number_1 f_phone_number_2
     1      1234567890        1233854100        4110256565
     2      0707070770        7895120044



My initial approach works but I was hoping there is a more efficient what of doing this.

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
Seems like there are up to two rows for f and one for b.

In that case you can simplify it to a single GROUP BY using conditional aggregation:

SELECT legacy_id,
   -- first number 'f'
   Min(CASE WHEN phone_type = 'f' THEN phone_number END) AS f_phone_number_1,
   -- first number 'b'
   Min(CASE WHEN phone_type = 'b' THEN phone_number END) AS b_phone_number_1,
   -- when MIN and MAX are different return the 2nd number
   NullIf(Max(CASE WHEN phone_type = 'f' THEN phone_number END)
         ,Min(CASE WHEN phone_type = 'f' THEN phone_number END)) AS f_phone_number_2
FROM table
GROUP BY 1


Edit: 

Of yourse you can't use the MAX/MIN approach when there's more than two rows per type.

But you can reduce the ranks down to one and move the case to the outer select:

SELECT legacy_id,
   Max(CASE WHEN phone_type = 'f' AND rn = 1 THEN phone_number END) AS f_phone_number_1,
   Max(CASE WHEN phone_type = 'f' AND rn = 2 THEN phone_number END) AS f_phone_number_2,
   Max(CASE WHEN phone_type = 'f' AND rn = 3 THEN phone_number END) AS f_phone_number_3,
   Max(CASE WHEN phone_type = 'f' AND rn = 4 THEN phone_number END) AS f_phone_number_4,
   Max(CASE WHEN phone_type = 'b' AND rn = 1 THEN phone_number END) AS b_phone_number_1,
   Max(CASE WHEN phone_type = 'b' AND rn = 2 THEN phone_number END) AS b_phone_number_2,
   Max(CASE WHEN phone_type = 'b' AND rn = 3 THEN phone_number END) AS b_phone_number_3,
   Max(CASE WHEN phone_type = 'b' AND rn = 4 THEN phone_number END) AS b_phone_number_4
FROM 
 (
   SELECT legacy_id,
      phone_type,
      phone_number,
      Dense_Rank() 
      Over (PARTITION BY a.legacy_id, a.phone_type 
            ORDER BY a.phone_number) AS rn
   FROM tab
 ) AS dt
GROUP BY 1