Hi!

Anybody who knows how to create crosstab query in SQL Server. I created a sample crosstab query in MS Access 2000 and I tested the same generated SQL statement in Query Analyzer but it fails. Any help?

-Dhodie

I am not sure what causes the failure, as you havent posted your code or not given any hint about what you were trying to achieve. I am not sure, if you were trying to achieve that in SQL server ot MS Access.

Still you can take a look at this page.
Cross-Tab Reports

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Hi Vijay!

Thanks for the immediate response.

Anyway, I have the following SQL statement generated by MS Access 2K.

TRANSFORM Sum(dbo_btBatchDailyActual.BDAQuantity) AS SumOfBDAQuantity
SELECT dbo_btBatchDailyActual.IONumber, dbo_btBatchDailyActual.MONumber, dbo_btBatchDailyActual.BatchNumber, Sum(dbo_btBatchDailyActual.BDAQuantity) AS [Total Of BDAQuantity]
FROM dbo_btBatchDailyActual
GROUP BY dbo_btBatchDailyActual.IONumber, dbo_btBatchDailyActual.MONumber, dbo_btBatchDailyActual.BatchNumber
PIVOT Format([BDADate],"Short Date");

I tried to execute this statement in Query Analyzer but it failed. I just thought that this code can't be easily executed in Query Analyzer and will need some revisions.
I was just thinking of the possible equivalent of the statement above in T-SQL.

-Dhodie

There is no "Transform" function in sql server. You can use the case predicate. You can read on it in books on line.



Sal