3
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.