You can achieve the desired result using a combination of the ROW_NUMBER() function and conditional aggregation. Here’s a SQL query that accomplishes your goal: WITH RankedContacts AS ( SELECT Key2 AS [Key], Contact, ROW_NUMBER() OVER (PARTITION BY Key2 ORDER BY Rank) AS RowNum FROM TableB ) SELECT [Key], MAX(CASE WHEN RowNum = 1 THEN Contact END) AS contact1, MAX(CASE WHEN RowNum = 2 THEN Contact END) AS contact2, MAX(CASE WHEN RowNum = 3 THEN Contact END) AS contact3 FROM RankedContacts GROUP BY [Key]; Result:   This query first ranks the contacts based on their rank for each key. Then, it uses conditional aggregation to assign the appropriate contacts to contact1, contact2, and contact3. A more extensive discussion of this issue can be found here: row number in sql server ... View more