Getting The Latest Data From Different Fields In One SQL - SQL Server 2000

Hi,

I'm trying to write some SQL which will give me the following result from the table below..

Currently I can do each of the PRICEx fields in a seperate query then join the results together. I do this by getting the MAX(Date) WHERE PRICEx IS NOT NULL then using that date to get PRICEx, and then repeat until I've done all the PRICE fields.

This is acceptable for a small selection, but for a large selection this can get quite slow. I was just wondering if there is there a way to do this in one single SQL without having to join the table to itself 3 times (for all 3 PRICE fields)







Source Table:





ProductID
MarketID
Date
PRICE1
PRICE2
PRICE3

1
2
1/01/2006
2.45
3.4
2.97

1
2
2/01/2006
2.51
3.5
NULL

1
2
3/01/2006
NULL
3.6
NULL

1
2
4/01/2006
NULL
NULL
NULL

Result:



ProductID
MarketID
PRICE1
PRICE2
PRICE3

1
2
2.51
3.6
2.97







Thanks,

Victor.