Search code examples
sqlsql-serversql-server-2012

How to cross join the the left side table to the right table

I need to cross join the data with one column with distinct name with all other rows data , as in the image: Left side with main table Value Right side is the result i need. already tried with cross join but in this case it is not working properly, we have at one scenario with more than 1.5 million records.

Bene column(B1,B2) will main column , and corresponding A1 with 5 values, against B1, so, 5*5 25 rows should be insert to another table for B1. (how much alert are there corresponding (B1,B2), and that must be repeated in the resulted column)

enter image description here

Solution

  • Although it was not clear in your description, it appears from the way you highlighted your expected results that you wish to cross join all ALERT values with all CUSTOMER/TXN pairs for each BENE.

    This can be done by first splitting out the values into two views (eliminating possible duplicates) and then joining them back together based on the common BENE values.

    The query below uses Common Table Expressions (CTEs) to extract two views or slices of your original data and join them back together to achieve the desired results.

    ;WITH Alerts AS (
    SELECT DISTINCT BENE, ALERT
    FROM DATA
    WHERE ALERT IS NOT NULL
), Customers AS (
    SELECT DISTINCT BENE, CUSTOMER, TXN
    FROM DATA
    WHERE (CUSTOMER IS NOT NULL OR TXN IS NOT NULL)
)
SELECT COALESCE(A.BENE, C.BENE) AS BENE, A.ALERT, C.CUSTOMER, C.TXN
FROM Alerts A
FULL OUTER JOIN Customers C
    ON C.BENE = A.BENE
ORDER BY BENE, ALERT, CUSTOMER, TXN

    See this db<>fiddle for a demo with some extra test data.