Feel the power of the new T-SQL

Just thinking if I could come up with a query that will use most, if not all, of the T-SQL enhancements of SQL Server 2005, using the adventureworks database here. And this was the best I could come up with.

Lets say I have a function that will get me the top n totaldue amount for a given customer.
Now I write a query to get the top three total due for each of the customer, rank them and pivot it and display it as one row per customer with one column per rank having the total due and use pagination for the display.

Here it goes :)

declare @pageNum int,
@RowsPerPage int

set @pageNum = 2
Set @RowsPerPage = 20;

WITH CTE as
(
SELECT C.CustomerID,
O.TotalDue,
rank() over (partition by C.CustomerID order by TotalDue) as rankOfSale
FROM
AdventureWorks.Sales.Customer AS C
CROSS APPLY
AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
)
Select * from
(
SELECT row_number() over (order by CustomerID) as rownum,
CustomerID,
[1] as Rank1,
[2] as Rank2,
[3] as Rank3
from
CTE
pivot(
sum(TotalDue) for rankOfSale in ([1],[2],[3])
) as pvt
) as P
where rownum between
(@pageNum - 1)*@RowsPerPage + 1
and @pageNum*@RowsPerPage

I am just wondering if I could even visualize such result in SQL Server 2000 (Anyone to take up the challenge). Boy, Microsoft is just getting us more and more lazier :)
What say guys?


CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS int, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = @custid
ORDER BY TotalDue DESC
GO