qcccc qcccc - 10 days ago 5
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.

Answer

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".