Slagter
May 21st, 2001, 01:34 AM
Hi !
I use this following query today in Access 97, but I need to convert it to be used with SQL Server 2000. But then I discovered that there is no such thing as a PIVOT table in SQL Server. What can I use instead ?????
TRANSFORM Count(*) AS X SELECT StopCodes.StopCode, StopCodes.Description, Count(*) FROM TestControl, TestLog, StopCodes
WHERE (TestControl.TestID = TestLog.TestID) AND
(TestControl.MaxRunNr = TestLog.RunNr) AND
(TestLog.StopCode = StopCodes.StopCode) AND
(TestLog.When >= #" & AccessDate(dateFrom, True) & "#) AND (TestLog.When <= #" & AccessDate(dateTo, False) & "#)
(TimeValue(TestLog.When) <= TimeSerial("iToHour & ", " & iToMin & ", 59))
GROUP BY StopCodes.StopCode, StopCodes.Description
PIVOT TestSpecName
I use this following query today in Access 97, but I need to convert it to be used with SQL Server 2000. But then I discovered that there is no such thing as a PIVOT table in SQL Server. What can I use instead ?????
TRANSFORM Count(*) AS X SELECT StopCodes.StopCode, StopCodes.Description, Count(*) FROM TestControl, TestLog, StopCodes
WHERE (TestControl.TestID = TestLog.TestID) AND
(TestControl.MaxRunNr = TestLog.RunNr) AND
(TestLog.StopCode = StopCodes.StopCode) AND
(TestLog.When >= #" & AccessDate(dateFrom, True) & "#) AND (TestLog.When <= #" & AccessDate(dateTo, False) & "#)
(TimeValue(TestLog.When) <= TimeSerial("iToHour & ", " & iToMin & ", 59))
GROUP BY StopCodes.StopCode, StopCodes.Description
PIVOT TestSpecName