Try this correlated subquery:

Select distinct status,
10=(Select Count(MovementNo) from BigTable where size=10
AND status=Big.status),
20=(Select Count(MovementNo) from BigTable where size=10
AND status=Big.status)
30=(Select Count(MovementNo) from BigTable where size=10
AND status=Big.status)
from bigTable big

Let me know if it worked

Here is also another less pretty way:

Select status="Loaded",
10=(Select Count(MovementNo) from BigTable where size=10
AND status="Loaded"),
20=(Select Count(MovementNo) from BigTable where size=10
AND status="Loaded")
30=(Select Count(MovementNo) from BigTable where size=10
AND status=="Loaded")
from bigTable big
UNION ALL
Select status="Empty",
10=(Select Count(MovementNo) from BigTable where size=10
AND status="Empty"),
20=(Select Count(MovementNo) from BigTable where size=10
AND status="Empty")
30=(Select Count(MovementNo) from BigTable where size=10
AND status=="Empty")
from bigTable big



>-----Original Message-----
>Hi,
>
>The problem:
>I have an SQL table containing around 250,000 rows. Among
others it includes
>the columns:
>* Size - May be "10", "20" or "40"
>* Status - May be either "Loaded" or Empty".
>* MovementNo - The numeric primary key.
>
>Now I would like to create a crosstab style query to
display the information
>as such:
>
>Status 10 20 40
>----------------------------
>Loaded 89000 20000 10000
>Empty 67000 43000 12000
>
>Where the numeric value is a count of MovementNo.
>
>I have the Mary Chipman book which contains an example,
however, I'm having
>difficulty in applying it to my requirements.
>
>However, I'm fortunate that in this example, the column
and row headers
>never change.
>
>Can anyone please point me in the right direction on how
to do this.
>
>Any advice is gratefully received.
>
>Kind thanks
>
>Chris S
>
>
>.
>