SQL - How to replace certain duplicated values with null

Question

I'm trying to null the values for BOMSEQ_0, CPNITMREF_0, YREF_0 and TEXTE_0 from both parts when the ITMBPS details duplicate the values down.

    SELECT B.BOMSEQ_0, case when B1.ITMREF_0 IS NOT NULL then NULL else B.CPNITMREF_0 end as CPNITREF, case when B1.ITMREF_0 IS NOT NULL then NULL else B.YREF_0 end as YREF, case when B1.ITMREF_0 IS NOT NULL Then NULL else CONVERT(varchar(255), T.TEXTE_0) end as TEXTE, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.YSTOFCY_0 end as YSTOFCY, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.PIO_0 end as PIO, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.BPSNUM_0 end as BPSNUM, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.ITMREFBPS_0 end as ITMREFBPS,
           B1.BOMSEQ_0, B1.CPNITMREF_0, B1.YREF_0, CONVERT(varchar(255), T.TEXTE_0) as TEXTE2, I.YSTOFCY_0, I.PIO_0, BPSNUM_0, I.ITMREFBPS_0
    FROM [BOMD]  B
    INNER JOIN [BOMD] B1
    ON B.CPNITMREF_0=B1.ITMREF_0 
    AND B.BOMALT_0=B1.BOMALT_0
    LEFT JOIN [TEXCLOB] T
    ON B1.BOMTEXNUM_0=T.CODE_0
    INNER JOIN [ITMBPS] I
    ON B1.CPNITMREF_0=I.ITMREF_0
    WHERE B.ITMREF_0='50'
      and B.BOMALT_0= 1
    UNION ALL
    SELECT B.BOMSEQ_0, B.CPNITMREF_0, B.YREF_0, CONVERT(varchar(255), T.TEXTE_0), I.YSTOFCY_0, I.PIO_0, I.BPSNUM_0, I.ITMREFBPS_0,
           NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
    FROM [BOMD] B
    LEFT JOIN [TEXCLOB] T
    ON B.BOMTEXNUM_0=T.CODE_0
    INNER JOIN [ITMBPS] I
    ON B.CPNITMREF_0=I.ITMREF_0
    WHERE B.ITMREF_0='50'
      and B.BOMALT_0= 1
    ORDER BY B.BOMSEQ_0, B1.BOMSEQ_0, YSTOFCY, YSTOFCY_0, PIO, PIO_0

I've tried using lag but I can't seem to get it to work. Edit: with example data and results (shortened slightly to fit but hopefully you get the idea).

BOMD
     ITMREF_0  CPNITMREF_0  BOMSEQ_0 BOMALT_0 YREF_0 BOMTEXNUM
        50        120-001      1        1        F1  1
        50        112-001      2        1        F2
        50        110-001      3        1        F3
        112-001   113-001      1        1        P2
        112-001   113-002      2        1        P3  2
        50        120-001      1        2        D1
        50        112-001      2        2        D2
        50        110-001      3        2        D3
        112-001   113-001      1        2        Q2
        112-001   113-004      2        2        Q3



TEXTCLOB
        CODE_0   TEXTE_0
        1        F1-10
        2        P3-10

ITMBPS
        ITMREF_0 YSTOFCY_0 BPSNUM_0 PIO_0
        120-001  UK        UK001    0
        120-001  GER       GER001   2
        112-001  UK        UK002    0
        112-001  GER       GER002   2
        110-001  GER       GER002   0
        113-001  GER       GER002   0
        113-001  UK        UK001    2
        113-002  UK        UK003    0

Desired Results

BOMSEQ CPNITMREF YREF BOMTEXNUM BPSNUM BOMSEQ  CPNITMREF YREF   BTEX  BPSNUM
1      120-001   F1   F1-10     UK001  NULL    NULL      NULL   NULL  NULL
NULL   NULL      NULL NULL      GER001 NULL    NULL      NULL   NULL  NULL       
2      112-001   F2   NULL      UK002  NULL    NULL      NULL   NULL  NULL
NULL   NULL      NULL NULL      GER002 NULL    NULL      NULL   NULL  NULL
NULL   NULL      NULL NULL      NULL   1       113-001   P2     NULL  GER002
NULL   NULL      NULL NULL      NULL   NULL    NULL      NULL   NULL  UK001
NULL   NULL      NULL NULL      NULL   2       113-002   P3     P3-10 UK003      
3      110-001   F3   NULL      GER002 NULL    NULL      NULL   NULL  NULL

Any ideas? Thanks

Answer 1

I solved this by using a case to compare the column against a lagged version. For example:

case when B.CPNITMREF_0=LAG(B.CPNITMREF_0,1,NULL) over (order by B.BOMSEQ_0, YSTOFCY_0, PIO_0, I.BPSNUM_0) then NULL else B.CPNITMREF_0 end as CPNITMREF