0

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,

Senko
  • 1
  • 1
  • You are wanting a PIVOT, specifically DYNAMIC PIVOT if there could be more than 2 contacts. [Here's one](http://stackoverflow.com/a/42076379/6167855) that I answered already but there are tons on SO. – S3S Feb 23 '17 at 15:46
  • Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – S3S Feb 23 '17 at 15:48
  • thank you scsimon I will give those a try – Senko Feb 23 '17 at 15:50
  • No worries at all @Senko – S3S Feb 23 '17 at 15:50

1 Answers1

0

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
Keval Pithva
  • 600
  • 2
  • 5
  • 21
  • I am getting 'GROUP_CONCAT' is not a recognized built-in function name. – Senko Feb 23 '17 at 15:11
  • that's because it's MySQL syntax not SQL Server. Keval was trying to do something similar to [this](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – S3S Feb 23 '17 at 15:44