Thanks for providing the solutions.  It's interesting to see how unions can be removed with cross join (good learning for the future).  sgmunson nice way to deliver the match type - thanks 🙂

I persisted the CTE and added the clustered index (nice optimisation - I will note for the future).   With 2.5m rows the query was too expensive - taking over 2 hours before I killed it. This matches my earlier experience.

There's been a change in requirements which let me reduce the data set to 65K rows.  After un-pivoting this grows to 260K.

Running the equi-join was very fast with the smaller dataset.

SQL Server parse and compile time:
 CPU time = 10 ms, elapsed time = 10 ms.
Table '#t_'. Scan count 10, logical reads 4160, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 22076, logical reads 429364, physical reads 0, read-ahead reads 553, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 20, logical reads 4984, physical reads 423, read-ahead reads 4561, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
 CPU time = 6155 ms, elapsed time = 2956 ms.Cheers.