预期的结果没有得到满足

只需使用条件聚合:

SELECT AttendeeID,
       MAX(CASE WHEN ProductPosition = 'PROD1' THEN product END) as prod1,
       MAX(CASE WHEN QuantityPosition = 'QUANTITY1' THEN quantity END) as quantity1,
       MAX(CASE WHEN ProductPosition = 'PROD2' THEN product END) as prod2,
       MAX(CASE WHEN QuantityPosition = 'QUANTITY2' THEN quantity END) as quantity2,
       MAX(CASE WHEN ProductPosition = 'PROD3' THEN product END) as prod3,
       MAX(CASE WHEN QuantityPosition = 'QUANTITY3' THEN quantity END) as quantity3
FROM (SELECT ptp.* ,
             row_number() over (partition by  AttendeeID, ProductPosition order by AttendeeID) as seqnum
      FROM #ProductTestingwithPosition2 ptp
     ) ptp
GROUP BY AttendeeID, seqnum;

PIVOT