SQL converting two address column into one

Question

I have a table

In result I am expecting only two columns Name and address ,for add1 and aad2 if they are similar i want to only show them once in address and for different aad1 and add2 I want to show them twice in two different rows I am expecting this output

Answer 1

I like to unpivot using cross apply. In your case, you can use logic like this:

select t.name, v.add
from t cross apply
     (values (1, add1), (2, add2)) v(n, add)
where n = 1 or (n = 2 and t.add1 <> t.add2);

That is, always take the first address. Take the second when it is different from the first.