CODEWITHSUNDEEP
sqlhana
Alex Sagayaraj
Alex Sagayaraj

Reputation: 21

Nullify the value of a column for multiple rows

I want to nullify a value of column for repeated rows.

Table1

Column1 column2 Column3 Measure1 
1032    45.201   091    1250

Table2 

Column1 column2  Column3 Column4 
1032    45.2018   091       20 
1032    45.2018   091       40

Query 

select 
A. Column1,
A. Column2,
case when B. Column4 is not null then B. Column4 else A. Column3 END AS Column 3 ,
A. Measure1,
from Table1 as A  
left outer join Table2 as B on
A.column1 = B.Column1 AND A.Column3 = B.Column3;

Output

Column1   column 2     Column 3    Measure1 
1032      45.2018       020         1250     
1032      45.2018       040         1250

Expected output

Column1   column 2     Column 3    Measure1 
1032      45.2018       020         1250     
1032      45.2018       040          0

How can I achieve the expected output?

Upvotes: 2

Views: 82

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try using Row_Number

SELECT A.Column1, A.Column2,
       CASE WHEN B.Column4 IS NOT NULL THEN B.Column4 ELSE A.Column3 END AS [Column3],
       CASE WHEN ROW_NUMBER() 
         OVER(PARTITION BY A.Column1, A.Column1, A.Measure1 ORDER BY A.Column1) = 1 
         THEN A.Measure1 ELSE 0 END AS Measure1
FROM Table1 AS A LEFT JOIN Table2 AS B ON A.column1 = B.Column1 AND A.Column3 = B.Column3;

SQL FIDDLE

Output

Column1   column 2     Column 3    Measure1 
1032      45.2018       020         1250     
1032      45.2018       040          0

Upvotes: 2

Related Questions