Posted by Alexander Kuznetsov on 07/14/06 16:15

Tom,

you might want to sort and aggregate a narrow result set first, then
join:

SELECT M.my_id, M.line_number,
code1,
code2,
code3,
code4
FROM Main_Table M
LEFT JOIN (
select my_id,
MIN(CASE R.seq_no WHEN 1 THEN R.my_code END) code1,
MIN(CASE R.seq_no WHEN 2 THEN R.my_code END) code2,
MIN(CASE R.seq_no WHEN 3 THEN R.my_code END) code3,
MIN(CASE R.seq_no WHEN 4 THEN R.my_code END) code4
from Related_Table r
group by my_id
) R
ON M.my_id = R.my_id


In many cases it is dramatically faster. We were describing it in
article named "The Less SQL Server Sorts, the Faster It Responds".

[Back to original message]