SQL Server eliminate function call and replace with join

Question

I am trying to rewrite the "Query" part by removing the function.
Since the table has more than 800k records, the function is called thrice for all 800k records.
I want to eliminate function and replace with JOIN?

--Function
CREATE FUNCTION [dbo].[MapValue] 
(
    @ObjCode AS INT,
    @ObjName AS VARCHAR(50),
    @ObjValue AS INT
)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @returnValue AS VARCHAR(100)

    SELECT @returnValue = Value FROM dbo.tblMap
    WHERE ObjCode = @ObjCode AND ObjName = @ObjName AND ObjValue = @ObjValue

    -- Return the result of the function
    RETURN @returnValue

END

--Query
SELECT  ObjectId, ObjectTypeCode,
        dbo.MapValue(4, 'ACode', ACode) AS AType,
        dbo.MapValue(4, 'SCode', SCode) AS SCode,
        dbo.MapValue(4, 'PCode', PCode) AS PCode
FROM    dbo.APoint
WHERE   ObjectTypeCode = 1

Here is the query that I came up using JOIN.
Is there any better way to do this? Can we use just ONE join instead of three?

--Modified query
SELECT  ObjectId, ObjectTypeCode,
        A.Value,
        s.Value,
        p.Value
FROM    dbo.APoint ap
    LEFT JOIN tblMap A ON A.ObjCode = 4 AND A.ObjName = 'ACode' AND A.ObjValue = ap.TypeCode
    LEFT JOIN tblMap s ON s.ObjCode = 4 AND s.ObjName = 'SCode' AND s.ObjValue = ap.TypeCode
    LEFT JOIN tblMap p ON p.ObjCode = 4 AND p.ObjName = 'PCode' AND p.ObjValue = ap.TypeCode
WHERE   ObjectTypeCode = 1

Answer 1

You could use case and a single left join:

SELECT  ObjectId, ObjectTypeCode,
        CASE WHEN map.ObjName = 'ActivityTypeCode' THEN map.Value END AS AType,
        CASE WHEN map.ObjName = 'statecode' THEN map.Value END As SCode,
        CASE WHEN map.ObjName = 'PriorityCode' THEN map.Value END As PCode
FROM    dbo.APoint ap
LEFT JOIN tblMap map ON map.ObjCode = 4 
                    AND map.ObjName IN('ActivityTypeCode', 'statecode', 'PriorityCode') 
                    AND map.ObjValue = ap.ActivityTypeCode
WHERE   ObjectTypeCode = 1
GROUP BY ObjectId, ObjectTypeCode