Create new columns depending on the columns in a table

Question

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

Answer 1

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

• LetUsSeng: I should have added that there are cases where the are two or more f and b phone number (about 3 or 4). Your script only assumes that there are two of each.
• dnoeth: @LetUsSeng: You should show your actual requirements and not a stripped down version.
• LetUsSeng: Thanks, sorry for not mentioning cases when there are more than 2.