ChrisM@Work (4/26/2013)[/b
Look at the work you will have to do when you want to bring each of those phone numbers into line with a parent row:
SELECT p.*,[mobile 1]= f1.PhoneNum,
[mobile 2]= f2.PhoneNum,
[home]= f3.PhoneNum,
[office 1]= f4.PhoneNum,
[office 2]= f5.PhoneNum
FROM Persons p
LEFT JOIN PhoneNumbers f1
ON f1.PersonID = p.PersonID AND f1.DeviceType = 'mobile 1'
LEFT JOIN PhoneNumbers f2
ON f2.PersonID = p.PersonID AND f2.DeviceType = 'mobile 2'
LEFT JOIN PhoneNumbers f3
ON f3.PersonID = p.PersonID AND f3.DeviceType = 'home'
LEFT JOIN PhoneNumbers f4
ON f4.PersonID = p.PersonID AND f4.DeviceType = 'office 1'
LEFT JOIN PhoneNumbers f5
ON f5.PersonID = p.PersonID AND f5.DeviceType = 'office 2'
SELECT p.*, x.*
FROM Persons p
CROSS APPLY (
SELECT
[mobile 1]= MAX(CASE WHEN f1.DeviceType = 'mobile 1' THEN PhoneNum END),
[mobile 2]= MAX(CASE WHEN f1.DeviceType = 'mobile 2' THEN PhoneNum END),
[home]= MAX(CASE WHEN f1.DeviceType = 'home' THEN PhoneNum END),
[office 1]= MAX(CASE WHEN f1.DeviceType = 'office 1' THEN PhoneNum END),
[office 2]= MAX(CASE WHEN f1.DeviceType = 'office 2' THEN PhoneNum END)
FROM PhoneNumbers f1
WHERE f1.PersonID = p.PersonID
) x
It is not necessary to query per device type, as only the parent ID and parent type can be supplied to return X amount of records of varying phone types.
SELECT * FROM PhoneNumbers WHERE Parent = 'ManagerUniqueID' AND ParentType = 'Manager'