TSQL equivalent of an MS Access Crosstab query

Question

What's the equivalent of an MS-Access crosstab query in TSQL? And Is there a better way?

I have a data organised like this:

Fish
ID   Name
---- ---------
1    Jack
2    Trout
3    Bass
4    Cat

FishProperty
ID   FishID Property Value
---- ------ -------- -----
1    1      Length   10
2    1      Girth    6
3    1      Weight   4
4    2      Length   6
5    2      Weight   2
6    3      Girth    12

I have a number of users who need to do reporting on the data and (obviously) it would be easier for them if they could see it like this:

Fish
ID   Name      Length Girth Weight
---- --------- ------ ----- ------
1    Jack      10     6     4
2    Trout     6            2
3    Bass             12

My plan was to create a crosstab-like view that they could report on directly.

Answer 1

Are you looking for PIVOT?

Edit: You may have to get to the second page before you see the usage of the PIVOT syntax.

Edit 2: Another example.

Example:

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

Edit 3 CodeSlave, take a look at this blog entry for some more information concerning dynamic pivot queries.