How to return multiple columns based on lowest score in table a, and join to flag eligibility in table b

You might consider the following strategy if you’re looking for a faster or more efficient approach, especially for large datasets.

1> Filter Eligible Products: Use a series of JOIN operations to directly filter and transform the data.

2> Compute Scores and Sort: Combine, compute scores, and sort in a more straightforward manner.

3> Extract Top Scores: Use aggregation and subqueries to get the lowest scores.

    -- Filter eligible products and calculate scores
WITH FilteredScores AS (
    SELECT 
        A.CUST_NO,
        COALESCE(NULLIF(B.DF_NOCA_FP, 0), 0) * A.ca_enpv AS ca_enpv,
        COALESCE(NULLIF(B.DF_NoCC, 0), 0) * A.cc_enpv AS cc_enpv,
        COALESCE(NULLIF(B.DF_NoHI, 0), 0) * A.hi_enpv AS hi_enpv,
        COALESCE(NULLIF(B.DF_NOLI, 0), 0) * A.in_enpv AS in_enpv,
        COALESCE(NULLIF(B.DF_NoInv, 0), 0) * A.mg_enpv AS mg_enpv,
        COALESCE(NULLIF(B.DF_NoMTG, 0), 0) * A.pl_enpv AS pl_enpv,
        COALESCE(NULLIF(B.DF_NoLOA, 0), 0) * A.pr_enpv AS pr_enpv,
        COALESCE(NULLIF(B.DF_NoSAV, 0), 0) * A.sv_enpv AS sv_enpv
    FROM TableA A
    JOIN TableB B ON A.CUST_NO = B.InvolvedPartyId_Numeric_ID
),
-- Get the lowest two scores for each customer
TopTwoScores AS (
    SELECT 
        CUST_NO,
        Product,
        Score
    FROM (
        SELECT 
            CUST_NO,
            'ca_enpv' AS Product, ca_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'cc_enpv' AS Product, cc_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'hi_enpv' AS Product, hi_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'in_enpv' AS Product, in_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'mg_enpv' AS Product, mg_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'pl_enpv' AS Product, pl_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'pr_enpv' AS Product, pr_enpv AS Score FROM FilteredScores
        UNION ALL
        SELECT 
            CUST_NO,
            'sv_enpv' AS Product, sv_enpv AS Score FROM FilteredScores
    ) AS AllScores
    WHERE Score > 0 -- Only consider positive scores
),
Ranked AS (
    SELECT 
        CUST_NO,
        Product,
        Score,
        ROW_NUMBER() OVER (PARTITION BY CUST_NO ORDER BY Score ASC) AS rank
    FROM TopTwoScores
)
-- Select the lowest two scores for each customer
SELECT 
    CUST_NO,
    Product,
    Score
FROM Ranked
WHERE rank <= 2
ORDER BY CUST_NO, rank;

Explaining Steps:

1> FilteredScores: This step computes the score for each product and handles eligibility in one go by multiplying the score with the eligibility flag. Using COALESCE(NULLIF(...)) ensures that only eligible products have non-zero scores.

2> TopTwoScores: This step unpivots the scores into a single column for easier sorting and filtering.

3> Ranked: This step ranks the products for each customer based on the score.

4> Final Selection: This query retrieves the top 2 scores for each customer.

This solution uses an unpivot. I added a 713209406 row to TableB to create at least 1 example for testing. I assume no score > 998.

WITH CTE AS (
    SELECT 
        A.CUST_NO,
        CASE WHEN IsNull(B.DF_NOCA_FP, 0) = 0 THEN 999 ELSE A.ca_enpv END AS ca_enpv,
        CASE WHEN IsNull(B.DF_NoCC, 0) = 0 THEN 999 ELSE A.cc_enpv END AS cc_enpv,
        CASE WHEN IsNull(B.DF_NoHI, 0) = 0 THEN 999 ELSE A.hi_enpv END AS hi_enpv,
        CASE WHEN IsNull(B.DF_NOLI, 0) = 0 THEN 999 ELSE A.in_enpv END AS in_enpv,
        CASE WHEN IsNull(B.DF_NoInv, 0) = 0 THEN 999 ELSE A.mg_enpv END AS mg_enpv,
        CASE WHEN IsNull(B.DF_NoMTG, 0) = 0 THEN 999 ELSE A.pl_enpv END AS pl_enpv,
        CASE WHEN IsNull(B.DF_NoLOA, 0) = 0 THEN 999 ELSE A.pr_enpv END AS pr_enpv,
        CASE WHEN IsNull(B.DF_NoSAV, 0) = 0 THEN 999 ELSE A.sv_enpv END AS sv_enpv
    FROM TableA A
    INNER JOIN TableB B ON  B.InvolvedPartyId_Numeric_ID=A.CUST_NO
),
ColValues AS (
SELECT DISTINCT CUST_NO, column_name as Product, v  as ColValue 
FROM CTE 
  UNPIVOT (v for column_name in (ca_enpv, cc_enpv, hi_enpv, in_enpv, mg_enpv, pl_enpv, pr_enpv, sv_enpv)) 
  upvt 
),
Numbered AS (
    SELECT CUST_NO, Product, ColValue,
        ROW_NUMBER() OVER (PARTITION BY CUST_NO ORDER BY ColValue ASC) AS rn
    FROM ColValues
)
SELECT CUST_NO, Product, ColValue
FROM Numbered
WHERE rn < 3
ORDER BY CUST_NO, rn;

fiddle

You can use CROSS APPLY(VALUES ...) to unpivot your data after joining the two tables. From there, ineligible products can be filtered out and the RANK() window function can be used to order the data by ascending product scores. The final select includes results with a rank of 2 or less (lowest product scores).

It what not clear from the data which product scores corresponded to which eligibility flags, so the following query pairs them up in source-table column order.

WITH RankedScores AS (
    SELECT
        A.CUST_NO, UNPVT.Product, UNPVT.Score,
        RANK() OVER(PARTITION BY A.CUST_NO ORDER BY UNPVT.Score) AS Rank
    FROM TableA A
    JOIN TableB B ON B.InvolvedPartyId_Numeric_ID = A.CUST_NO
    CROSS APPLY(
        VALUES
            ('ca_enpv', A.ca_enpv, B.DF_NOCA_FP),
            ('cc_enpv', A.cc_enpv, B.DF_NoCC),
            ('hi_enpv', A.hi_enpv, B.DF_NoHI),
            ('in_enpv', A.in_enpv, B.DF_NOLI),  -- ??? Unsure about this pairing
            ('mg_enpv', A.mg_enpv, B.DF_NoInv), -- ??? Unsure about this pairing
            ('pl_enpv', A.pl_enpv, B.DF_NoMTG), -- ??? Unsure about this pairing
            ('pr_enpv', A.pr_enpv, B.DF_NoLOA), -- ??? Unsure about this pairing
            ('sv_enpv', A.sv_enpv, B.DF_NoSAV)
    ) UNPVT(Product, Score, Eligible)
    WHERE UNPVT.Eligible = 1
)
SELECT R.CUST_NO, R.Product, R.Score, R.Rank
FROM RankedScores R
WHERE R.Rank <= 2
ORDER BY R.CUST_NO, R.Rank

Sample results (using modified ID values and some extra data):

The RANK() will allow for ties (as for the CUST_NO = 999 case above). If you would prefer to ignore ties and only select a maximum of two products per customer, you can replace RANK() with ROW_NUMBER().

If desired, the products can be combined into a single row per customer using GROUP BY and STRING_AGG().

WITH RankedScores AS (...)
SELECT
    R.CUST_NO,
    STRING_AGG(R.Product, ',') WITHIN GROUP(ORDER BY R.Rank) AS Products
FROM RankedScores R
WHERE R.Rank <= 2
GROUP BY R.CUST_NO
ORDER BY R.CUST_NO

See this db<>fiddle for a demo.