qcccc qcccc - 6 months ago 39
MySQL Question

How can I achieve the result I want using sql statement?

I have some data in my database. I tried to do a retrieval and it should appear in such a way.

Current table:

FName| lName | phoneNo | year
Tom | Tan | 9123456 | 1
Tom | Tan | 9012345 | 1 <----extra row

What I want:

FName | lName| phoneNo| year | phoneNo2
Tom | Tan | 9123456| 1 |9012345

How can I achieve this using sql statement?

I would like to see if FName and lName are duplicated, then the duplicated data will be combined as such the phoneNo will be added into one column called phoneNo2.


Do a GROUP BY, use MIN() to get first phoneno and MAX() to get the second.

select FName, lName, min(phoneNo), max(year), max(phoneNo)
from tablename
group by FName, lName

You can also do a self LEFT JOIN:

select t1.FName, t1.lName, t1.phoneNo, t1.year, t2.phoneNo
from tablename t1
left join tablename t2
    on t1.FName = t2.FName and t1.lName = t2.lName
    and t1.phoneNo < t2.phoneNo

Note: According to ANSI SQL YEAR is a reserved word, so you may need to delimit it as "year".