Click here to Skip to main content
15,070,111 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?
Posted
Comments
Gerry Schmitz 21-Mar-21 11:13am
   
I think "pivot" is the word you're looking for. Like, pivot table3 on oexp_id, before trying to join with it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900