From Jet SQL to SQL server SQL
Posted on 2000-03-17
I'm a developer floundering in the world of the DBA. I need to pass a SQL string through ADO that will return records in a CROSS-TAB type fashion. I can do it in access using the following
FROM dbo_ProductSpec INNER JOIN dbo_TestSpec ON dbo_ProductSpec.TestID = dbo_TestSpec.TestID
GROUP BY dbo_TestSpec.TestID, dbo_ProductSpec.SourceID
ORDER BY dbo_TestSpec.TestID;
to create an Access query/view QA_Test
then use the following
TRANSFORM Max(dbo_TestResult.Result) AS MaxOfResult
FROM QA_Tests LEFT JOIN dbo_TestResult ON QA_Tests.TestID = dbo_TestResult.TestID
GROUP BY dbo_TestResult.ProductNo
to give the view I want. I can't however do this in SQL server. I can create the QA_Tests view OK but the second query won't run in SQL. Are the TRANSFORM and PIVOT functions valid in SQL server.
I would be grateful of a push in the right direction.