This afternoon is busy transferring the cross table in the Access database to the SQL Server database. In SQL Server, the Transfrom method is not supported, finds relevant information, finds the case method for conversion
code is shown below:
Table Landundrtake structure is shown below:
Form APPRAISER structure is shown below:
Access code:
Copy codecode is as follows:
Transform first (Landundrtake.Valuerid) as valuerid first FIRST
SELECT Appraiser.quarterId, Landundertake.landCode
FROM Landundertake INNER JOIN Appraiser ON (Landundertake .valuerId = Appraiser.valuerID) AND (Landundertake .quarterId = Appraiser.quarterId)
GROUP BY Appraiser.quarterId, Landundertake .landCode
PIVOT Landundertake .valuerGrade;
SELECT Appraiser.quarterId, Landundertake.landCode
FROM Landundertake INNER JOIN Appraiser ON (Landundertake .valuerId = Appraiser.valuerID) AND (Landundertake .quarterId = Appraiser.quarterId)
GROUP BY Appraiser.quarterId, Landundertake .landCode
PIVOT Landundertake .valuerGrade;
SQL Server code:
Copy codecode is as follows:
select dbo.appraiser.quarterID,dbo.landUndertake.landcode,case dbo.landUndertake.appraiserGrade when ‘appraiserID1’ then dbo.landundertake.appraiserID else null END as appraiserID,case dbo.landUndertake.appraiserGrade when ‘appraiserID2’ then dbo.landundertake.appraiserID else null END as appraiserID1
from dbo.LandUndertake inner join
dbo.Appraiser ON dbo.LandUndertake.quarterID=dbo.appraiser.quarterID and dbo.landundertake.appraiserID=DBO.Appraiser.appraiserID
Group by dbo.appraiser.quarterID,dbo.landUndertake.landcode,dbo.landundertake.appraiserGrade,dbo.landundertake.appraiserID
from dbo.LandUndertake inner join
dbo.Appraiser ON dbo.LandUndertake.quarterID=dbo.appraiser.quarterID and dbo.landundertake.appraiserID=DBO.Appraiser.appraiserID
Group by dbo.appraiser.quarterID,dbo.landUndertake.landcode,dbo.landundertake.appraiserGrade,dbo.landundertake.appraiserID
cross -table query results are shown below:
