Replace duplicate values only in consecutive records with NULL

— Replace duplicate values only in consecutive records with NULL [duplicate]–

SELECT
CASE
WHEN lag(tran_id) over(order by NULL) = tran_id
THEN NULL
ELSE tran_id
END tran_id,
CASE
WHEN lag(tran_name) over(order by NULL) = tran_name
THEN NULL
ELSE tran_name
END tran_name,
flag
FROM t;

TRAN_ID TRAN_N F
———- —— –
101 Lend A
B
C
D
102 Borrow E
101 Lend F
G

7 rows selected.

SELECT
CASE
WHEN lag(A.CLIENTID) over(order by NULL) = A.CLIENTID
THEN NULL
ELSE A.CLIENTID
END CLIENTID,
CASE
WHEN lag(A.QUOTEID) over(order by NULL) = A.QUOTEID
THEN NULL
ELSE A.QUOTEID
END QUOTEID,
A.CLIENT_TYPE,
B.FISCAL_YEAR
FROM creditcheck_indv A
LEFT OUTER JOIN CREDIT_FISCAL_YEARS B ON A.CLIENTID=B.CLIENTID

CLIENTID          QUOTEID          FISCAL_YEAR
——–                     ——–                   ————
12345                   2222                   10/SEP/18
14/SEP/17

Leave a Reply