How to convert Access Query to SQL Query (Crosstab/PIVOT)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to convert Access Query to SQL Query (Crosstab/PIVOT)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-10 : 09:43:44

oranthor writes "I am not an expert at SQL, I can do the minor lookups and such... Anyway I need to convert this Access Query (CrossTab?/PIVOT?) to SQL. I have not been able to get it to work as of yet. I have gotten as far as getting the right rows and columns but haven't been able to get the data correct.

Access Query:
PARAMETERS <forms]!Reporting![CurrentDay> DateTime;
TRANSFORM Sum(Extracts.Cash) AS SumOfCash
SELECT
Extracts.Unit
FROM
Units INNER JOIN Extracts ON Units.Desig = Extracts.Unit
WHERE
(((DateDiff("d",[Date],[forms]![Reporting]![CurrentDay]))<30) AND ((Units.StoreStatus) In (3,4)) AND ((Extracts.Type) ="M" ))
GROUP
BY Extracts.Unit
PIVOT DateDiff("d",[Date],forms!Reporting!CurrentDay) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29);

Any help would be greatly appreciated!! I have read through the BOL and Access articles, but having problems..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources