Emulating a crosstab query in SQL

[DCrake]

Can anyone give me an example of a view that resembles a crosstab query from Access. I am aware that SQL does not support crosstab queries but everyone I speak to says that what can be done in Access and be replicated in SQL.

CodeMaster:

 

[namliam]

use a decode or case (dont know the equivalent SQL statement)

I.e.
Select Field1
, Sum( Case when SomeField = 1 then Value else 0 end ) as FieldIs1
, Sum( Case when SomeField = 2 then Value else 0 end ) as FieldIs2
, Sum( Case when SomeField = 3 then Value else 0 end ) as FieldIs3
, Sum( Value ) as Total
From anytable join anotherTable on Key = ForeignKey
where Clause = "something"
Group by Field1

I hope that is clear enough... It is not pretty and on quite as flexible... but it works.

 

[FoFa]

Try this link for a little explaination