# 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

• 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. Oct 12 '16 at 19:56
• @LetUsSeng: You should show your actual requirements and not a stripped down version. Oct 12 '16 at 20:03
• Thanks, sorry for not mentioning cases when there are more than 2. Oct 12 '16 at 20:27