Hi

You could try

WITH RecommendationRank AS (

SELECT ItemCode As OriginalItem,

AlternateItem,

Row_Number() over (Partition by ItemCode Order by Date desc) as RecNumber

From AlternateRecommendations

)

SELECT

o.ItemCode

,MAX(CASE WHEN r.RecNumber = 1 THEN r.AlternateItem ELSE NULL END) Alt1

,MAX(CASE WHEN r.RecNumber = 2 THEN r.AlternateItem ELSE NULL END) Alt2

,MAX(CASE WHEN r.RecNumber = 3 THEN r.AlternateItem ELSE NULL END) Alt3

,MAX(CASE WHEN r.RecNumber = 4 THEN r.AlternateItem ELSE NULL END) Alt4

,MAX(CASE WHEN r.RecNumber = 5 THEN r.AlternateItem ELSE NULL END) Alt5

,MAX(CASE WHEN r.RecNumber = 6 THEN r.AlternateItem ELSE NULL END) Alt6

,MAX(CASE WHEN r.RecNumber = 7 THEN r.AlternateItem ELSE NULL END) Alt7

,MAX(CASE WHEN r.RecNumber = 8 THEN r.AlternateItem ELSE NULL END) Alt8

FROM OriginalItem o

CROSS APPLY (SELECT TOP 8 * FROM RecommendationRank r WHERE o.ItemCode = r.ItemCode ORDER BY r.RecNumber) a

GROUP BY o.ItemCode;