I have the following table:
Name Type Value
---------------------
mike 开发者_开发问答 phone 123
mike address nyc
bob address nj
bob phone 333
I want to have the result like this:
name value value
-------------------
mike nyc 123
bob nj 333
How can I do it?
it is called a self-join. 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 query assumes that each name has an address, but phone numbers are optional.
Something like this:
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);
精彩评论