SELECT
	Name, 
	L1 AS HomePhone,
	L2 AS WorkPhone,
	L3 AS CellPhone
FROM
	Tbl
PIVOT( MAX(PhoneNumber) FOR PhoneLocation IN ([L1],[L2],[L3]))P

.....
PIVOT( MAX(PhoneNumber) FOR PhoneLocation IN ([L1],[L2],[L3])) AS P

SELECT DISTINCT 
       TOP(100) PERCENT mts.MembershipTypeStatusName,
       p.PersonFirstName,
       p.PersonLastName,
       CASE 
            WHEN dbo.Attribute.AttributeName = 'POW' THEN 'Yes'
            ELSE 'No'
       END                              AS PowerMember,
       MAX(CASE 
            WHEN PhoneLocationPrecedence IN (1, 2) THEN PhonePhoneNumber
            ELSE '-'
       END)                              AS Home_phone,
       MAX(CASE 
            WHEN PhoneLocationPrecedence IN (5, 6) THEN PhonePhoneNumber
            ELSE '-'
       END)                                AS Cell_phone,
       MAX(CASE 
            WHEN PhoneLocationPrecedence IN (3, 4) THEN PhonePhoneNumber
            ELSE '-'
       END)                               AS Work_phone
FROM   dbo.PersonPhone
       INNER JOIN dbo.Membership        AS mm
       INNER JOIN dbo.MembershipAgreement AS ma
            ON  mm.MembershipGUID = ma.MembershipGUID
       INNER JOIN dbo.Agreement         AS a
            ON  ma.AgreementGUID = a.AgreementGUID
       INNER JOIN dbo.MembershipTypeStatus AS mts
            ON  mm.MembershipTypeStatusGUID = mts.MembershipTypeStatusGUID
       INNER JOIN dbo.Member AS m
            ON  mm.MemberGUID = m.MemberGUID
       INNER JOIN dbo.Person            AS p
            ON  m.PersonGUID = p.PersonGUID
       INNER JOIN dbo.MembershipStatus  AS s
            ON  mm.MembershipGUID = s.MembershipGUID
            AND mts.MembershipTypeStatusGUID = s.MembershipTypeStatusGUID
            ON  dbo.PersonPhone.PersonGUID = p.PersonGUID
       INNER JOIN dbo.Phone
            ON  dbo.PersonPhone.PhoneGUID = dbo.Phone.PhoneGUID
       INNER JOIN dbo.PhoneLocation
            ON  dbo.Phone.PhoneLocationGUID = dbo.PhoneLocation.PhoneLocationGUID
       LEFT OUTER JOIN dbo.Attribute
       INNER JOIN dbo.MemberAttribute
            ON  dbo.Attribute.AttributeGUID = dbo.MemberAttribute.AttributeGUID
            ON  m.MemberGUID = dbo.MemberAttribute.MemberGUID
WHERE  (mts.MembershipTypeStatusName = N'Active')
       AND (
               ma.MembershipAgreementLastUpdate =(
                   SELECT MAX(MembershipAgreementLastUpdate) AS Expr1
                   FROM   dbo.MembershipAgreement
                   WHERE  (MembershipGUID = mm.MembershipGUID)
               )
           )
       AND (
               s.MembershipStatusLastUpdate =(
                   SELECT MAX(MembershipStatusLastUpdate) AS Expr1
                   FROM   dbo.MembershipStatus
                   WHERE  (MembershipGUID = s.MembershipGUID)
               )
           )
       OR  (mts.MembershipTypeStatusName = N'Active')
GROUP BY
       mts.MembershipTypeStatusName,
       p.PersonFirstName,
       p.PersonLastName,
       CASE 
            WHEN dbo.Attribute.AttributeName = 'POW' THEN 'Yes'
            ELSE 'No'
       END	
ORDER BY
       p.PersonFirstName