Convert Access crosstab query to SQL Server equivalent

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

Typical record:

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
jonnyboy69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mjm42Commented:
Maybe I don't understand your problem but why don't the following work:

SELECT MONTH(Timestamp), COUNT(keyFUser) AS Users FROM tblTrans
WHERE YEAR(Timestamp) = 2004
GROUP BY MONTH(Timestamp)
ORDER BY MONTH(Timestamp)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
muzzy2003Commented:
That's my understanding of the question as well, I don't think you're going mad!
0
jonnyboy69Author Commented:
Awww crap I am going mad ... it's been a long day alright! ;)

The last in a long list of errors from someone elses code and it looked hard! So I thought i'd get someone else to do it. Please have 500 points.

/switches brain on
0
muzzy2003Commented:
Why do I never see these questions quickly enough? ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.