TSQL equivalent of an MS Access Crosstab query

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.

From stackoverflow

• 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.

  CodeSlave : And if I don't know ahead of time that the properties were Oranges and Pickles (I might have a list of properties available though). Can I do anything like a "select *"?

  TheTXI : Codeslave, check the newest edit for a blog entry that goes into doing dynamic pivots.

• As an alternative for a PIVOT query, you could create a view like this:

  create view dbo.myview
  as
  select 
      fish.ID
  ,   fish.Name
  ,   len.Value as Length
  ,   gir.Value as Girth
  ,   wei.Value as Weight
  from fish
  left join fishproperty len 
      on len.fishid = fish.id 
      and len.property = 'Length'
  left join fishproperty gir 
      on gir.fishid = fish.id 
      and gir.property = 'Girth'
  left join fishproperty wei 
      on wei.fishid = fish.id 
      and wei.property = 'Weight'
  

  If you don't know the columns ahead of time, the only solution I can think of is to generate the query dynamically and use "exec sp_executesql" to run it. For example:

  declare @query nvarchar(4000)
  set @query = 'select ... from fish'
  -- For each column
  set @query = @query + ' left join fishproperty ' +
      propname + ' on ' + propname + '.fishid = fish.id ' +
      'and ' + propname + '.property = ''' + propname + ''' '
  exec sp_executesql @query
  

  Warning: dynamic queries can be a maintenance headache.

• It's a duplicate of this question: http://stackoverflow.com/questions/801635/help-needed-with-dynamic-pivoting-in-sql2005 and yes, it's possible to do with dynamic sql, with some tricks you'll even be able to do a select over results returned or create a view for it, but if your table has more than 1000 records, I would not recommend this approach as performance would be poor at best.

• You can also use the CASE statement but your reporting tool should be able to do this for you ( dynamically! ) with the matrix/pivot/crosstab template.