1

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
  • What database are you using? – Taryn Aug 6 '14 at 12:24
  • 1
    Which DBMS are you using? Did you try anything yourself? Why didn't it work? – Colin 't Hart Aug 6 '14 at 12:32
6

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)
  • You can remove the name column from the y table, it's not needed. – ypercubeᵀᴹ Aug 7 '14 at 8:57
  • @ybercube, you're right, I'll update the answer – Lennart Aug 7 '14 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.