Selecting data from multiple rows and insert it into a column

Question

My table is like this:

temp1
name   city     phone_no    pincode
-----------------------------------
amit   Delhi     12345       123
Rajiv  Bombay   836536       432

How do I transform the last three columns into a single column, address, like in the following?

name     address
----------------
amit      Delhi
amit      12345
amit        123
rajiv    Bombay
rajiv    836536
rajiv       432

Answer 1

The elegance of the answer will vary with the DBMS of your choice. In it's simplest form:

select name, city as address from temp1
union
select name, phone_no as address from temp1
union
select name, pincode as address from temp1

I believe this should be supported by most DBMSes. If your DBMS supports lateral (cross apply in sqlserver?) you can do something like:

select x.name, y.address
from temp x 
join lateral ( values (x.name,x.city)
                    , (x.name,x.phone_no)
                    , (x.name,x.pincode)) y(name,address) 
    on x.name = y.name

Edit: The T-SQL (SQL Server) syntax uses CROSS APPLY to perform the same task, i.e.:

SELECT x.name, y.address
FROM temp x 
CROSS APPLY ( VALUES (x.city),
                     (x.phone_no),
                     (x.pincode)
            ) AS y(address) 

CROSS APPLY in SQL Server also often generates more effective execution plans than a bunch of union operators, so it's definitely worth a try.

Edit: As @ypercube suggests it is not necessary to join, removing the name column and taking the cartesian product gives same result

select x.name, y.address 
from temp x 
cross join lateral ( 
    values (x.city),(x.phone_no),(x.pincode)
) y(address)