What's a clean way to join tables with Entity-Attribute-Value?

I am wondering if I'm doing this in the most efficient way possible.

I have a database table that houses custom data elements. To see what records use the data in the custom object table I have to join on 3 ID numbers, as well as specify the description. It's not that bad, but sometimes I can have up to 30 of these joins in my SQL script and that can be long. I was wondering if there is a cleaner way to code this? Maybe with a temp stored procedure or temp function?

SELECT
    r.ID, 
    apples.status, 
    oranges.status, 
    bananas.status 
FROM
    record r
LEFT JOIN 
    custom_object apples ON apples.T_ID1 = r.T_ID1
                         AND apples.T_ID2 = r.T_ID2
                         AND apples.T_ID3 = r.T_ID3
                         AND apples.DESC = 'Apples'
LEFT JOIN 
    custom_object oranges ON oranges .ID1= r.T_ID1
                          AND oranges.T_ID2 = r.T_ID2
                          AND oranges.T_ID3 = r.T_ID3
                          AND oranges.DESC = 'Oranges'
LEFT JOIN 
    custom_object bananas ON bananas.T_ID1= r.T_ID1
                          AND bananas.T_ID2 = r.T_ID2
                          AND bananas.T_ID3 = r.T_ID3
                          AND bananas.DESC = 'Bananas'

Using a single join with a case expression seems like a much simpler approach here.

SELECT
    r.ID, 
    AppleStatus = case when co.[DESC] = 'Apples' then co.status end,
    OrangeStatus = case when co.[DESC]= 'Oranges' then co.status end, 
    BananaStatus = case when co.[DESC] = 'Bananas' then co.status end 
FROM record r
LEFT JOIN custom_object co ON co.T_ID1 = r.T_ID1
                         AND co.T_ID2 = r.T_ID2
                         AND co.T_ID3 = r.T_ID3
                         AND co.[DESC] in ('Apples', 'Oanges', 'Bananas')

Conditional aggregation is a possibility:

SELECT
    r.ID,
    MAX(CASE WHEN co.[DESC] = 'Apples' THEN co.status END) AS applesStatus, 
    MAX(CASE WHEN co.[DESC] = 'Oranges' THEN co.status END) AS orangesStatus, 
    MAX(CASE WHEN co.[DESC] = 'Bananas' THEN co.status END) AS bananasStatus
FROM
    record r
LEFT JOIN 
    custom_object co ON co.T_ID1 = r.T_ID1
                         AND co.T_ID2 = r.T_ID2
                         AND co.T_ID3 = r.T_ID3
                         AND co.[DESC] IN ('Apples', 'Oranges', 'Bananas') -- optional
GROUP BY r.ID

The same inside an OUTER APPLY will avoid the outer GROUP BY and allow more flexibility as you build out the rest of your query.

SELECT
    r.ID,
    statuses.*
FROM
    record r
OUTER APPLY ( 
    SELECT
        MAX(CASE WHEN co.[DESC] = 'Apples' THEN co.status END) AS applesStatus, 
        MAX(CASE WHEN co.[DESC] = 'Oranges' THEN co.status END) AS orangesStatus, 
        MAX(CASE WHEN co.[DESC] = 'Bananas' THEN co.status END) AS bananasStatus
    FROM custom_object co
    WHERE co.T_ID1 = r.T_ID1
        AND co.T_ID2 = r.T_ID2
        AND co.T_ID3 = r.T_ID3
        AND co.[DESC] IN ('Apples', 'Oranges', 'Bananas') -- optional
) statuses

A Common Table Expression (CTE) can also be used:

; WITH statuses AS (
    SELECT
        co.T_ID1, co.T_ID2, co.T_ID3,
        MAX(CASE WHEN co.[DESC] = 'Apples' THEN co.status END) AS applesStatus, 
        MAX(CASE WHEN co.[DESC] = 'Oranges' THEN co.status END) AS orangesStatus, 
        MAX(CASE WHEN co.[DESC] = 'Bananas' THEN co.status END) AS bananasStatus
    FROM custom_object co
    WHERE co.[DESC] IN ('Apples', 'Oranges', 'Bananas') -- optional
    GROUP BY co.T_ID1, co.T_ID2, co.T_ID3
)
SELECT
    r.ID,
    s.applesStatus,
    s.orangesStatus,
    s.bananasStatus
FROM
    record r
LEFT JOIN 
    statuses s ON s.T_ID1 = r.T_ID1
               AND s.T_ID2 = r.T_ID2
               AND s.T_ID3 = r.T_ID3

The above all assume no duplicate properties per ID combination.

For best performance, you should define an index on custom_object(T_ID1, T_ID2, T_ID3, [DESC]).

Sample results:

See this db<>fiddle.