LearnItFirst User Forum

;
WITH  Guardian
AS
(
    SELECT
     sc.StudentID,
     GuardianName=REPLACE(scp.FirstName + ' ' + scp.LastName,',',' '),

     ROW_NUMBER() OVER(PARTITION BY sc.StudentID ORDER BY addr.State DESC, scp.LastName, scp.FirstName) AS rn

    FROM StudentContact sc
        INNER JOIN Person scp ON scp.PersonID = sc.ContactID
        LEFT OUTER JOIN Address addr ON addr.AddressID = scp.MailingAddressID
        LEFT OUTER JOIN PersonTelephone pt ON pt.PersonID = scp.PersonID AND pt.IsPrimary = 'True'
    WHERE sc.IsCustodian = 1 
)
select *
from guardian

PIVOT (
FOR RN
IN ([1], [2])

) AS pvt