How can I retrieve a recordset from a matrix-like table?

I have a table defined as (int Row, int Column, money Data); as it were a matrix.

Which is the more efficient way of retrieving a result set with the following form?

Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...


Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.

How can I get a row count of CTE recordset?

Hi everybody,
I want to get a row count of temporary CTE recordset.
My query is like this:

With SQLPaging As
(
Select Row_number() Over (Order by Row_num) as RowNumber,
Row_num,
Column_A,
Column_B From OriginalTable
)
Select * from SQLPaging Where RowNumber between 21 and 30

Let's assume SQLPaging definition contains 1,000 rows.

If I use @.@.rowcount, it returns only 10. I.e. number of actual rows per page.
What I want is that I want to know row count of SQLPaging CTE without extra counting query.
I think SQLServer already knows how many rows CTE definition contains.
The reason that I don't want to use extra counting query is that if SQLPaging definition has row performance query (like wild card(%) character search), extra row counting query takes simillar amount of loads. (Select count(*) from SQLPaging)

Is there any way of getting actual CTE definition row count?

Thanks.

Try using count(*) over()

With SQLPaging As
(
Select Row_number() Over (Order by Row_num) as RowNumber,
count(*) over() as MaxNum, --<<<<
Row_num,
Column_A,
Column_B From OriginalTable
)
Select * from SQLPaging Where RowNumber between 21 and 30

|||Yes, use Mark's answer - it's most definitely the one you want.

The great thing about this is that you won't find yourself having a massive performance hit from doing it this way. It will do it in the same pass.

Rob|||Cool! Thanks guys. I really appreciate it. :)