Convert Join Result to Two Column with SQL

Question

I have a JOIN Result as following

Address1 Address2 Address3
ABC       XYZ      LMN

I want to convert them to following format

Address    Level

ABC         1
XYZ         2
LMN         3

Answer 1

You will need to UNPIVOT or UNION ALL. These take your columns and converts it into rows. A UNION ALL can be done on all RDBMS:

select address1 Address, 1 as level
from yourtable
union all
select address2 Address, 2 as level
from yourtable
union all
select address3 Address, 3 as level
from yourtable

See SQL Fiddle with Demo

If you are using an RDBMS with the UNPIVOT function (SQL Server 2005+/Oracle 11g+) then your query will be similar to this:

select Address, 
  right(Level, 1) Level
from yourtable
unpivot
(
  address
  for level in (Address1, Address2, Address3)
) unpiv

See SQL Fiddle with Demo

The result for both is:

| ADDRESS | LEVEL |
-------------------
|     ABC |     1 |
|     XYZ |     2 |
|     LMN |     3 |