Sql append 2 rows in one table

Question

I have the following table:

Name Type Value --------------------- mike phone 123 mike address nyc bob address nj bob phone 333 

I want to get the result as follows:

 name value value ------------------- mike nyc 123 bob nj 333 

How can i do this?

Answer 1

it is called self-union. the trick is to use aliases.

 select address.name, address.value as address, phone.value as phone from yourtable as address left join yourtable as phone on address.name = phone.name where address.type = 'address' and (phone.type is null or phone.type = 'phone') 

The request assumes that each name has an address, but phone numbers are optional.

Answer 2

Something like that:

 SELECT a.name AS name, phone, address FROM (SELECT name, value AS phone FROM mytable WHERE type = "phone") AS a JOIN (SELECT name, value AS address FROM mytable WHERE type = "address") AS b ON(a.name = b.name);