N_Muller (12/16/2013)

---

You are correct. The query won't work without an aggregate. However, going back to my original question on the usage of sub-queries. So far I have the two options, using the cross apply or sub-query. I'm sure there are others, but I haven't figured those out yet. The sub-query approach takes a lot longer to generate the query plan when compared to the cross apply where compilation takes no time at all. However, once the query plan is built running the query repeatedly is much faster than the cross apply. The query is embedded in a stored procedure which is called with different parameters. Also, my problem isn't as simple as this example, which I built for illustration. I actually have 96 columns and thousands of rows. My question is "is there a way to get the compilation time of the cross apply and the execution time of the sub-query?" Thanks in advance to anyone that can help me.

Cross apply method:

selectX.IndexID,

X.Status1,

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

X.Status2,

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

X.Status3,

Priority3 = sum(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

Sub-query method:

selectX.IndexID,

X.Status1,

Priority1 = ( select top 1 PriorityID from @spTable where StatusID = X.Status1 ),

X.Status2,

Priority2 = ( select top 1 PriorityID from @spTable where StatusID = X.Status2 ),

X.Status3,

Priority3 = ( select top 1 PriorityID from @spTable where StatusID = X.Status3 )

from@tbl X