July 27, 2004 at 8:40 am
I'm trying to convert an Access database to SQL Server. I've successfully imported the tables and am now trying to use the Query Analyzer to ensure the queries will work accordingly. My SQL Server skills are at the novice level at best, so please bear with me.
Below is the SQL Code from the Access database copied into the SQL Query Analyzer along with the error messages I'm getting when I Parse the query.
TRANSFORM Count(D_INMAIN.YEAR) AS [The Value]
SELECT [CONTRACTOR INFO].[DEP CONTRACTOR HIRED], Count(D_INMAIN.YEAR) AS [Total Of YEAR]
FROM D_INMAIN LEFT JOIN [CONTRACTOR INFO] ON D_INMAIN.IN_CASENO = [CONTRACTOR INFO].IN_CASENO
GROUP BY [CONTRACTOR INFO].[DEP CONTRACTOR HIRED]
ORDER BY [CONTRACTOR INFO].[DEP CONTRACTOR HIRED]
PIVOT D_INMAIN.[I\C];
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'D_INMAIN'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'PIVOT'.
Thanks for any help you can provide.
July 27, 2004 at 8:49 am
This one's simple enough- there's no TRANSFORM or PIVOT functions in SQL - these are access functions.
it is possible to make a pivot table, but you have to do this by using CASE statements
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
Hope this helps
MVDBA
July 27, 2004 at 8:52 am
Thank you very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply