Help to remove NULL from output results

Question

Hello- I have to remove NULL for column-V2 in the results. All other results are ok. If current’s timeline data with previous timeline’s data are the same even both have NULL values but in the result should be blank as other are showing blank if there is no difference.

Create table #temp (P1 char(10), T1 char(4), O1 char(4), Edate datetime, E1 char(2), B1 char(2), L1 char(2), BE1 char(2), Z1 char(2), AR char(2), Te1 char(2), VL1 numeric(6,2), MN1 numeric(6,2),
V1 numeric(6,2), V2 numeric(6,2))

Insert into #temp values ('01','DDD','11','2018-01-01','12','A1','D1','12','AB','A1','60',250, 10,200,NULL)
Insert into #temp values ('01','DDD','11','2021-01-01','12','A1','D1','12','AB','A1','60',350, 10,200,NULL)
Insert into #temp values ('01','DDD','11','2022-01-01','12','A1','D1','12','AB','A1','65',450, 10,200,NULL)

Insert into #temp values ('02','RRR','11','2018-01-01','12','A2','D1','44','BB','A1','60',250, 10,200,300)
Insert into #temp values ('02','RRR','11','2021-01-01','12','A2','D1','44','BB','A1','60',350, 10,200,300)
Insert into #temp values ('02','RRR','11','2022-01-01','12','A2','D1','44','BB','A1','65',450, 10,200,300)
Insert into #temp values ('02','RRR','11','2022-02-01','12','A2','D1','46','BB','A1','65',550, 10,200,400)

Insert into #temp values ('08','WEW','11','2020-01-01','12','A2','D1','44','BB','A1','60',250, 10,200,NULL)
Insert into #temp values ('08','WEW','11','2021-01-01','12','A2','D1','44','BB','A1','60',250, 10,200,NULL)
Insert into #temp values ('08','WEW','11','2022-01-01','14','A2','D1','44','CC','A1','60',250, 10,200,NULL)

;WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY P1,T1,O1 ORDER BY Edate DESC) AS RNum
FROM #temp
)
SELECT A.P1,A.T1,A.O1,CAST(A.Edate AS DATE) AS Edate ,
CASE WHEN A.E1 = B.E1 THEN '' ELSE A.E1 END AS E1,
CASE WHEN A.B1 = B.B1 THEN '' ELSE A.B1 END AS B1,
CASE WHEN A.L1 = B.L1 THEN '' ELSE A.L1 END AS L1,
CASE WHEN A.BE1 = B.BE1 THEN '' ELSE A.BE1 END AS BE1,
CASE WHEN A.Z1 = B.Z1 THEN '' ELSE A.Z1 END AS Z1,
CASE WHEN A.AR = B.AR THEN '' ELSE A.AR END AS AR,
CASE WHEN A.Te1 = B.Te1 THEN '' ELSE A.Te1 END AS Te1,
CASE WHEN A.VL1 = B.VL1 THEN ''ELSE CAST(A.VL1 AS VARCHAR(20)) END AS VL1,
CASE WHEN A.MN1 = B.MN1 THEN '' ELSE CAST(A.MN1 AS VARCHAR(20)) END AS MN1,
CASE WHEN A.V1 = B.V1 THEN '' ELSE CAST(A.V1 AS VARCHAR(20)) END AS V1,
CASE WHEN ISNULL(NULL, A.V2)=ISNULL(NULL, B.V2) THEN '' ELSE CAST(A.V2 AS VARCHAR(20)) END AS V2
FROM CTE A JOIN CTE B ON A.P1=B.P1 AND A.T1=B.T1 AND A.O1=B.O1
WHERE (A.RNum =2 AND B.RNum =1) OR(A.RNum =1 AND B.RNum =2)

Output result attached.

Answer 1

Change this line:

CASE WHEN ISNULL(NULL, A.V2)=ISNULL(NULL, B.V2) THEN '' ELSE CAST(A.V2 AS VARCHAR(20)) END AS V2

to

CASE WHEN A.V2 = B.V2 THEN '' ELSE isnull(CAST(A.V2 AS VARCHAR(20)), '') END AS V2

Answer 2

;WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY P1,T1,O1 ORDER BY Edate DESC) AS RNum
FROM #temp
)
SELECT A.P1,A.T1,A.O1,CAST(A.Edate AS DATE) AS Edate ,
CASE WHEN A.E1 = B.E1 THEN '' ELSE A.E1 END AS E1,
CASE WHEN A.B1 = B.B1 THEN '' ELSE A.B1 END AS B1,
CASE WHEN A.L1 = B.L1 THEN '' ELSE A.L1 END AS L1,
CASE WHEN A.BE1 = B.BE1 THEN '' ELSE A.BE1 END AS BE1,
CASE WHEN A.Z1 = B.Z1 THEN '' ELSE A.Z1 END AS Z1,
CASE WHEN A.AR = B.AR THEN '' ELSE A.AR END AS AR,
CASE WHEN A.Te1 = B.Te1 THEN '' ELSE A.Te1 END AS Te1,
CASE WHEN A.VL1 = B.VL1 THEN ''ELSE CAST(A.VL1 AS VARCHAR(20)) END AS VL1,
CASE WHEN A.MN1 = B.MN1 THEN '' ELSE CAST(A.MN1 AS VARCHAR(20)) END AS MN1,
CASE WHEN A.V1 = B.V1 THEN '' ELSE CAST(A.V1 AS VARCHAR(20)) END AS V1,
CASE WHEN A.V2 IS NULL AND B.V2 IS NULL THEN '' ELSE ISNULL(CAST(A.V2 AS VARCHAR(20)), '') END AS V2
FROM CTE A JOIN CTE B ON A.P1=B.P1 AND A.T1=B.T1 AND A.O1=B.O1
WHERE (A.RNum =2 AND B.RNum =1) OR(A.RNum =1 AND B.RNum =2)

Tom