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

In oracle database and sql-server, union operator remove duplicate rows

select name, add1 as address from mytable
union
select name, add2 from mytable

Answer 2

A portable approach uses union all to unpivot the columns to rows and eliminate the duplicates:

select name, add1 as add from mytable
union all select name, add2 from mytable where add2 <> add1

Depending on your database, there may be a better option available. A typical solution is a lateral join - but the syntax highly depend on the database (and not all of them implement that feature).

---

Edit: in SQL Server:

select t.name, a.add
from mytable t
cross apply (values (add1), (case when add1 <> add2 then add2 end)) a(add)
where a.add is not null

Answer 3

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.