PIVOT, Row_Number() returning multiple rows
Try removing isPrimary from your SELECT where you're calculating the ROW_NUMBER().

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P




| ID                   | 1                  | 2                   | 3            |
|----------------------|--------------------|---------------------|--------------|
| B4100358183Q!ZH.ETES | ABoyd@Energize.com | BSykes@Energize.com | TC6@test.com |