Split row results to column

问题:

I am trying to create a query that pulls all customer info from our database, but because there are often multiple contact persons behind 1 company, I get the same company listed in multiple rows. I would like to have each company only show up in 1 row and place additional contact persons in new columns instead,

Current results:

Company    |   Contact person
-------------------------------
Company 1  |   Alex
Company 1  |   Tom
Company 2  |   James
Company 2  |   Andy

Desired results:

Company    |   Contact person 1  |  Contact person 2
----------------------------------------------
Company 1  |   Alex              |  Tom
Company 2  |   James             |  Andy

Here is approx. what my query looks like:

SELECT Customer.Company, CustomerAdditionalInfo.ContactPerson
FROM Customer LEFT OUTER JOIN CustomerAdditionalInfo

Thank you for your time,

回答1:

Try this...

 SELECT Customer.Company,GROUP_CONCAT(Customer.ContactPerson) AS 'AllContactPerson'
FROM Customer GROUP BY Customer.Company

this will give you as under result (if ContactPerson details comes from Customer table. )

Company    |   AllContactPerson 
--------------------------------
Company 1  |   Alex,Tom