Data in same row

Question

I have a table.

ID  Contact_Details   Pref
1   1234567890         A 
1   [email protected]      A
2   1234567891         A
2   [email protected]     B

simple select query will not solve this problem

select contact_details as mobile,contact_details as email 
from table 
where pref in (A,B);

I want a result like this:

ID Mobile      Pref  Email           Pref
1  1234567890   A    [email protected]    A
2  1234567891   A    [email protected]    B

Please note that I have different pref for email and mobile and I want that in the output.

Answer 1

You can write:

select t1.ID, t1.Contact_Details as Mobile, t1.Pref as PrefMobile,
       t2.Contact_Details as Email, t2.Pref as PrefEmail
from Table t1
inner join Table t2
on t1.ID = t2.ID
where t1.Contact_Details not like '%@%'
and t2.Contact_Details like '%@%'