Yes, my table is quite large and it does have primary keys, and I do generate the code automatically. In any case, here's a version that I ended up using which uses cross apply, instead of the case statement:

selectX.IndexID,

X.Status1,

Priority1 = case when PX.ColID = 1 then S.PriorityID end,

X.Status2,

Priority2 = case when PX.ColID = 2 then S.PriorityID end,

X.Status3,

Priority3 = case when PX.ColID = 3 then S.PriorityID end

from@tbl X cross apply

(

values( 1, X.Status1 ),

( 2, X.Status2 ),

( 3, X.Status3 )

) PX ( ColID, StatusID ) inner join

@spTable S on

S.StatusID = PX.StatusID

group by X.IndexID, X.Status1, X.Status2, X.Status3