0

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 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?

http://sqlfiddle.com/#!9/cacf4/15

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
DAC
  • 41
  • 7
  • What logic determines which data is "deviating"? Solution might involve calculating a row number for each group and using that value as column header in a PIVOT (CROSSTAB) query. Problem is you want to transpose 3 fields. I use MSAccess and don't know if SQLServer stored procedure can handle. Maybe following will give you ideas for SQLServer https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access and http://allenbrowne.com/ser-67.html#MultipleValues. – June7 Mar 20 '21 at 20:25
  • [dup on dba](https://dba.stackexchange.com/questions/287402/sql-issue-transposing-columns-with-same-id-different-values-not-constant) – SMor Mar 20 '21 at 21:32

0 Answers0