mysql - Join 2 tables and display by id,priority & number

Question

Example tables and desired result: The result table shown below is the output I actually want.

tried the following query with pivot:

with pivot_data AS
(
  select client_id
        ,ph_type
        ,Ph_number
  from client_table
    inner join phone_table
      on client_table.phone_id = phone_table.ph_id
)
select *
from pivot_data
pivot (sum(ph_number)
       for ph_type in ('c','w','h')
      );

Result I got:

Any help would be appreciated. Answers in sql server would be great but oracle & mysql is also welcome if they can point me in the right direction. :)
Thanks in advance.

Answer 1

Oracle Query:

SELECT *
FROM   (
  SELECT client_id, priority, phone_number, phone_type
  FROM   client_table c
         LEFT OUTER JOIN
         phone_table p
         ON ( c.phone_id = p.phone_id )
)
PIVOT ( MAX( phone_type ) AS phonetype, MAX( phone_number ) AS phonenumber
        FOR priority IN ( 1 AS Prio1, 2 AS Prio2, 3 AS Prio3 ) );

Output:

 CLIENT_ID PRIO1_PHONETYPE PRIO1_PHONENUMBER PRIO2_PHONETYPE PRIO2_PHONENUMBER PRIO3_PHONETYPE PRIO3_PHONENUMBER
---------- --------------- ----------------- --------------- ----------------- --------------- -----------------
         1 C               9999999999        H               5555555555        W               7777777777