The pure T-SQL solution:

SELECT

customer_key= c.customer_key

,home_phone= h.phone_number

,business_phone= b.phone_number

,mobile_phone= m.phone_number

FROMcustomer c

LEFT OUTER JOIN

phoneh

ONc.customer_key= h.customer_key

ANDh.phone_type= 'Home'

LEFT OUTER JOIN

phoneb

ONc.customer_key= b.customer_key

ANDb.phone_type= 'Business'

LEFT OUTER JOIN

phonem

ONc.customer_key= m.customer_key

ANDm.phone_type= 'Mobile'

Beware, as this solution introduces NULL values for customers that don't have a phone number for a certain phone type.

I used LEFT OUTER JOINS, because it is possible for a customer to have a mobile phone and a home phone, but not a business phone, for example.

If a customer has multiple phone numbers for a specific phone type, you will still get multiple rows (e.g. a family having multiple mobile phone numbers). How are you planning to incorporate that in your solution?

A small side question: why do you use the nolock hint everywhere in you SQL statements? Is there a specific reason for?