Merge SQL results by creating new field?

I'd like to take two records like this:

Id   Fname  Lname 开发者_C百科 Email
------------------------------------
1    John   Doe    jdoe@example.com
2    John   Doe    doej1@place.net

And combine them in such a way that I have only one record:

Id   Fname  Lname Email1            Email2
---------------------------------------------------
1    John   Doe   jdoe@example.com  doej@place.net

As you can see, this would add a field to my record that otherwise does not exist in the database. Is this possible or do I have to find another way?

NOTE: I am not able to change the database structure in any way.

The database product and version makes all the difference here. Assuming that you are using something that supports ranking functions (e.g. SQL Server 2005+)

Select FName, LName
    , Min( Case When Rnk = 1 Then Z.Email End ) As Email1
    , Min( Case When Rnk = 2 Then Z.Email End ) As Email2
From    (
        Select FName, LName, Email
            , Row_Number() Over ( Partition By FName, LName Order By Id ) As Rnk
        From MyTable
        ) As Z
Group By Z.FName, Z.LName

If you are not using a product that supports ranking functions (e.g. MySQL), then it is trickier. One solution that should work on almost all database systems would be:

Select FName, LName
    , Min( Case When Rnk = 1 Then Z.Email End ) As Email1
    , Min( Case When Rnk = 2 Then Z.Email End ) As Email2
From    (
        Select FName, LName, Email
            , (Select Count(*)
                From MyTable As T2
                Where T1.FName = T2.FName
                    And T2.LName = T2.LName
                    And T2.Id < T1.Id) + 1 As Rnk
        From MyTable As T1
        ) As Z
Group By Z.FName, Z.LName

If you're using SQL Server, see this article: http://support.microsoft.com/kb/175574

It's got an example with different data, but the task is the same. You can usually find examples for other RDBMS's by searching for "Rotate Table [rdbmstype]" on Google or Bing

    select x.Id as ID, x.Fname as Fname, x.Lname as Lname, x.Email as Email1, y.Email as email2 from
        (select id, fname, lname, email from table) as x inner join
        (select id, email from table) as y on x.id = y.id and x.email != y.email

Probably a little inefficient, but it'll get the job done.

In MySQL you can use GROUP_CONCAT():

SELECT Fname, Lname, GROUP_CONCAT(email) emails
FROM tbl
GROUP BY Fname, Lname;

If you need emails in separate fields, you can split resulting emails string in your programming environment (like explode(',', $result['emails']) in PHP), or use self-join (works only for 2 email fields):

SELECT a.Fname, a.Lname, a.Email Email1, b.email Email2
FROM tbl a JOIN tbl b ON a.Fname = b.Fname AND a.Lname = b.Lname AND a.Email != b.Email

adding contraption:

GROUP BY CONCAT(GREATEST(Email1,Email2),LEAST(Email1,Email2))

to get rid of duplicate email pairs.