0

I have a table called phones which I need to return the top three phone numbers, and three types for each c_no in six separate columns and then join to the contacts table. The phone table is laid out like so, 

p_no,c_no, phone, p_type

My desired result set would be,

c_no, First_Phone, First_Phone_Type, Second_Phone, Second_Phone_Type, Third_Phone, Third_Phone_Type

I tried the following, but it doesn't show anything in the second or third columns when I add the group by c_no

select  c_no,
        (case when cp_sort = 1 and cp_status = 1 then phone end) as Phone_1,
        (case when cp_sort = 2 and cp_status = 1 then phone end) as Phone_2,
        (case when cp_sort = 3 and cp_status = 1 then phone end) as Phone_3
 from   phones cp  
 group by
         c_no

Thank you guys so much in advance for all the help!

chase cabrera
  • 129
  • 11
  • you need a pivot table : http://stackoverflow.com/questions/7674786/mysql-pivot-table – Gabriel Rodriguez Sep 23 '15 at 20:08
  • I tried the following, but it doesn't show anything in the second or third columns when I add the group by c_no select c_no , (case when cp_sort = 1 and cp_status = 1 then phone end) as Phone_1 , (case when cp_sort = 2 and cp_status = 1 then phone end) as Phone_2 , (case when cp_sort = 3 and cp_status = 1 then phone end) as Phone_3 from phones cp group by c_no – chase cabrera Sep 23 '15 at 20:12

1 Answers1

0

You could try:

select distinct c_no, 
  (select phone from phones p1 where p0.c_no = p1.c_no and cp_sort = 1) Phone_1,
  (select phone from phones p2 where p0.c_no = p2.c_no and cp_sort = 2) Phone_2,
  (select phone from phones p3 where p0.c_no = p3.c_no and cp_sort = 3) Phone_3
from phones p0;

Might need some optimization to work well on a large set of data.

Phil
  • 2,797
  • 1
  • 24
  • 30
  • Thanks @Phil, What if I have multiple sort = 1 instead of just one for each contact? Is there a way I can take the p_no into account to get the max, max-1, and max-2? – chase cabrera Sep 23 '15 at 21:20
  • I'm not really sure how that would fit into your fixed structure of Phone_1, Phone_2, Phone_3. What does p_no represent? – Phil Sep 23 '15 at 21:27