Sql - issue transposing columns with same ID different values not constant

I have a scenario to transpose data values with same ID different values and same value different ID's

Table 1 : BE

| ID | NM | BE_ID |
| -- | -- | ----- |
| G1 | ABC | E1 |
| G2 | DEF | E2 |
| G3 | GHI | E3 |
| G4 | JKL | E4 |
| G5 | MNO | E5 |

Table 2 : EXF

| ID | NM | BE_ID | EXF_ID |
| -- | -- | ----- | ------ |
| G1 | ABC | E1 | EXF1 |
| G2 | DEF | E2 | EXF2 |
| G3 | GHI | E3 | EXF3 |
| G4 | JKL | E4 | EXF4 |
| G5 | MNO | E5 | EXF5 |

Table 3 : OXF

| OXF_ID | O_VAL | OEXF_ID |
| ------ | ----- | ------- |
| OXF1 | MC1 | EXF1 |
| OXF2 | MC2 | EXF1 |
| OXF3 | MC3 | EXF3 |
| OXF4 | MC4 | EXF4 |
| OXF5 | MC4 | EXF5 |

**Expected Output :** 


| RULE | ID | NM | VAL | DEVIATING_VAL_1 | DEVIATING_ID_1 | DEVIATING_NM_1 | DEVIATING_VAL N | DEVIATING_ID N | DEVIATING_NM N |
| ---- | -- | -- | --- | -------------- | -------------- | --------------- | ------------------ | -------------- | --------------- |
RULE1 | G1 | ABC | MC1 | MC2 | | | |
RULE2 | G4 | JKL | MC4 | | G5 | MNO | |


**Rule 1: Same ID Different Values**

Records with same ID and different values. 

As in the expected output, ID G1 has different values MC1 AND MC2 and only such cases are expected. ID G2 has no value and ID G3 has only 1 value which should not come in output

**Rule 2: Same Value Different ID**

Records with same value and different ID
As in the expected output, value MC4 is associated with different ID's G4 and G5.

What I have tried:

<pre>**What I tried:**

Below is the joining condition for the tables:

    SELECT BE.ID,BE.NM,OXF.O_VAL
    FROM BE
    LEFT JOIN EXF
    ON BE.BE_ID=EXF.BE_ID
    LEFT JOIN OXF
    ON EXF.EXF_ID=OXF.OEXF_ID;

    /* RULE 1 */
    SELECT 'RULE1' AS RULE,BE.ID,BE.NM,OXF.O_VAL AS VAL
    FROM BE
    LEFT JOIN EXF
    ON BE.BE_ID=EXF.BE_ID
    LEFT JOIN OXF
    ON EXF.EXF_ID=OXF.OEXF_ID
    WHERE BE.ID IN (
        SELECT BE.ID
        FROM BE
    LEFT JOIN EXF
    ON BE.BE_ID=EXF.BE_ID
    LEFT JOIN OXF
    ON EXF.EXF_ID=OXF.OEXF_ID
        GROUP BY BE.ID
        HAVING COUNT(distinct OXF.O_VAL) > 1
    )
    UNION
    /* RULE 2 */
    SELECT 'RULE2' AS RULE,BE.ID,BE.NM,OXF.O_VAL AS VAL
    FROM BE
    LEFT JOIN EXF
    ON BE.BE_ID=EXF.BE_ID
    LEFT JOIN OXF
    ON EXF.EXF_ID=OXF.OEXF_ID
    WHERE OXF.O_VAL IN (
        SELECT OXF.O_VAL
        FROM BE
    LEFT JOIN EXF
    ON BE.BE_ID=EXF.BE_ID
    LEFT JOIN OXF
    ON EXF.EXF_ID=OXF.OEXF_ID
        GROUP BY OXF.O_VAL
        HAVING COUNT(distinct BE.ID) > 1
    );



In both cases, I get records with same ID different values and vice versa but how do I transpose them when I don't know how many columns I might need too. Could some one please help me with it?