Just need a conversion of an Access crosstab query to a SQL Server equivalent.
The query is as follows:
TRANSFORM Count(tblTrans.keyFUser) AS CountOfkeyFUser SELECT Val(Month([Timestamp])) AS [Month] FROM tblTrans WHERE (((Year([Timestamp]))=2004)) GROUP BY Val(Month([Timestamp])) ORDER BY Val(Month([Timestamp])) PIVOT "Users";
tblTrans contains a record for each "transaction" on the site. So the query basically has a row for each month (with each month indicated by its numeric value) and the value of CountOfkeyFUser will be the number of "transactions" in that month.
The structure of tblTrans is as follows:
keyTrans - Int (identity, auto-increment)
keyFUser - Int
Type - SmallInt
ID - Int
Timestamp - smalldatetime
keyTrans = 63 (unique identifier of the transaction)
keyFUser = 24 (user that performed the transaction)
Type = 1 (type of transaction)
ID = 44 (product the transaction relates to)
Timestamp = 30/09/2004 12:37:05 (date/time transaction occurred)
Thanks in advance