I am trying to combine 4 data sets into one. The tables are linked by a person id and sample id. Two of those tables have a "specific same ID" field where I would like to create a new row for the each value in t4.specificsampleid and leave those blank. Similarly all t3 related columns would be blanks/nulls when t4 is populated. Any help would be greatly appreciated.

IDEAL OUTPUT:

CodePudding user response：

Ideally, you would provide the input table definitions and not just the expected output, but if I'm understanding correctly, I think you might just want to use a UNION ALL.

Something like:

SELECT t1.ID, t1.main_spec, t2.ind, t3.specificsampleid t3ssid, null t4ssid
FROM t1 INNER JOIN t2
  ON [Condition]
 INNER JOIN t3
  ON [Condition]
UNION ALL
SELECT t1.ID, t1.main_spec, t2.ind, null t3ssid, t4.specificsampleid t4ssid
FROM t1 INNER JOIN t2
  ON [Condition]
 INNER JOIN t4
  ON [Condition]

The first part will get all the rows with a match on t3 (and put null in the relevant t4 column), the second part will get all the rows with a match on t4 (and put null in the relevant t3 column), and the UNION ALL will combine them.

In other words, it will get two rows if there is a match in t3 and t4, or one row if there is only a match in one.

If you need rows where there is a match in neither t3 nor t4, you could add a third part after another UNION ALL or edit one of the existing parts to handle that case.

I'm sure there are alternatives, but I hope this is a good starting point for your problem.

CodePudding user response：

The WITH clause is here just for sample data generation and, as such, it is not the part of the answer.

I assume that the first table in your question is a result that you already have (as a view maybe) and that this resulting dataset is starting point to get the expected result.
Using just a union with crossed where conditions

WITH
    tbl AS
        (
            Select '10R46' "T1_ID", 'R1005' "T1_MAIN_SPEC", 'Y' "T2_IND", 'R1005AB' "T3_SPEC_ID", 'R1005_ST1' "T4_SPEC_ID" From Dual Union All
            Select '10R46' "T1_ID", 'R1005' "T1_MAIN_SPEC", 'Y' "T2_IND", 'R1005CD' "T3_SPEC_ID", 'R1005_TB12' "T4_SPEC_ID" From Dual Union All
            Select '10R46' "T1_ID", 'R1005' "T1_MAIN_SPEC", 'Y' "T2_IND", 'R1005EF' "T3_SPEC_ID", 'R1005_QZ9' "T4_SPEC_ID" From Dual
        )
SELECT 
    T1_ID "T1_ID", T1_MAIN_SPEC "T1_MAIN_SPEC", T2_IND "T2_IND", T3_SPEC_ID "T3_SPEC_ID", null "T4_SPEC_ID"
FROM 
    tbl  
WHERE
    T3_SPEC_ID Is Not Null
UNION ALL    
SELECT  
    T1_ID "T1_ID", T1_MAIN_SPEC "T1_MAIN_SPEC", T2_IND "T2_IND", Null "T3_SPEC_ID", T4_SPEC_ID "T4_SPEC_ID"
FROM 
    tbl  
WHERE 
    T4_SPEC_ID Is Not Null
--  
--  R e s u l t
--  
--  T1_ID T1_MAIN_SPEC T2_IND T3_SPEC_ID T4_SPEC_ID
--  ----- ------------ ------ ---------- ----------
--  10R46 R1005        Y      R1005AB               
--  10R46 R1005        Y      R1005CD               
--  10R46 R1005        Y      R1005EF               
--  10R46 R1005        Y                 R1005_ST1  
--  10R46 R1005        Y                 R1005_TB12 
--  10R46 R1005        Y                 R1005_QZ9

Using full outer join and some data transformation - here is the SQL and result.

WITH
    tbl AS
        (
            Select '10R46' "T1_ID", 'R1005' "T1_MAIN_SPEC", 'Y' "T2_IND", 'R1005AB' "T3_SPEC_ID", 'R1005_ST1' "T4_SPEC_ID" From Dual Union All
            Select '10R46' "T1_ID", 'R1005' "T1_MAIN_SPEC", 'Y' "T2_IND", 'R1005CD' "T3_SPEC_ID", 'R1005_TB12' "T4_SPEC_ID" From Dual Union All
            Select '10R46' "T1_ID", 'R1005' "T1_MAIN_SPEC", 'Y' "T2_IND", 'R1005EF' "T3_SPEC_ID", 'R1005_QZ9' "T4_SPEC_ID" From Dual
        )
SELECT DISTINCT
    T1_ID,
    T1_MAIN_SPEC,
    T2_IND,
    CASE WHEN B_T3_SPEC_ID Is Null And B_T4_SPEC_ID Is Null THEN T3_SPEC_ID ELSE Null END "T3_SPEC_ID",
    CASE 
        WHEN CASE WHEN B_T3_SPEC_ID Is Null And B_T4_SPEC_ID Is Null THEN T3_SPEC_ID ELSE Null END Is Null 
        THEN T4_SPEC_ID 
    ELSE Null
    END "T4_SPEC_ID"
FROM
    (
        SELECT 
             a.T1_ID, a.T1_MAIN_SPEC, a.T2_IND, 
             a.T3_SPEC_ID, 
             a.T4_SPEC_ID,
             CASE WHEN a.T4_SPEC_ID = b.T4_SPEC_ID THEN b.T3_SPEC_ID ELSE Null END "B_T3_SPEC_ID", 
             CASE WHEN a.T3_SPEC_ID <> b.T3_SPEC_ID THEN Null ELSE b.T4_SPEC_ID END "B_T4_SPEC_ID"
        FROM
            tbl a
        FULL OUTER JOIN
            tbl b ON (b.T1_ID = a.T1_ID And b.T1_MAIN_SPEC = a.T1_MAIN_SPEC And b.T2_IND = a.T2_IND)
    )
ORDER BY
    T1_ID,
    T1_MAIN_SPEC,
    T2_IND
--  
--  R e s u l t
--  
--  T1_ID T1_MAIN_SPEC T2_IND T3_SPEC_ID T4_SPEC_ID
--  ----- ------------ ------ ---------- ----------
--  10R46 R1005        Y      R1005AB               
--  10R46 R1005        Y      R1005CD               
--  10R46 R1005        Y      R1005EF               
--  10R46 R1005        Y                 R1005_QZ9  
--  10R46 R1005        Y                 R1005_ST1  
--  10R46 R1005        Y                 R1005_TB12                         

Regards...