n to n relationship how to obtain a result in one row

Question

so I have an employee that can work in many companies so I have an n to n relationship, how can I obtain the companies that one employee works in, in just one row with sql?

example
table - employee
Employeeid  employeename
1             mike

table company
companyId  CompanyName
1           cocacola
2             nokia
3              intel

table employeeCompany
id   employeeid  companyid
1       1            1
2       1            2
3       1            3

I thought with this but can´t

select Employeeid  , companyid 
from employeeCompany 
where employeeid  = 1 
group by Employeeid  , companyid

i expected mike, cocacola, nike, intel in one row so the employee mike work in that company's using sql 2008 with stored procedure i guest i can do it but I want a more simple solution if there is — Diego_DX, Apr 24 '12 at 18:34

score 2 · Accepted Answer · answered Apr 24 '12 at 18:55

Easiest way to do it in Sql Server is by use of FOR XML PATH. The cryptic part .value('text()[1]','nvarchar(max)') handles special xml characters.

select employee.*, companies.*
from Employee
OUTER APPLY
(
    select stuff ((SELECT ', ' + Company.CompanyName
      FROM EmployeeCompany
        INNER JOIN Company
           ON EmployeeCompany.CompanyId = Company.CompanyID
      WHERE EmployeeCompany.employeeid = Employee.EmployeeID
      ORDER BY Company.CompanyName
      FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)')
    , 1, 2, '') Companies
) companies

See demo at Sql Fiddle.

score 0 · Answer 2 · edited May 23 '17 at 12:12

It sounds like you want something that is similar to mySQL's Group_Concat in SQL Server?

If you are looking for a way to do this so that each companyid is in a separate column, then that would only be possible with some difficulty using dynamic SQL. At which time it might be easier to just return this to an application and let it handle what it needs within its own logic?

BTW, the dynamic SQL logic would go something like this in case you were wondering...notice how nasty it is...thus why I would suggest against it.

select @highestCount = max(count(*))
from employeeCompany
group by Employeeid

declare createtemptable varchar(max), @filltableselect varchar(max), @filltablejoin varchar(max)
declare @currentCount int
set @currentCount = 0
set @createtemptable = 'CREATE TABLE #Temp (EmployeeID INT'
set @filltableselect = 'INSERT INTO #Temp SELECT EmployeeCompany0.EmployeeID, EmployeeCompany0.CompanyID'
set @filltablejoin = 'FROM EmployeeCompany AS EmployeeCompany0'

while(@currentCount < @highestCount)
begin
    set @createtemptable = @createtemptable + ', CompanyID' 
        + CAST(@currentCount AS VARCHAR(2)) + ' INT'

    if(@currentCount > 0)
    begin
        set @filltableselect = @filltableselect + ', EmployeeCompany' 
            + CAST(@currentCount AS VARCHAR(2)) + '.CompanyId'

        set @filltablejoin = @filltablejoin 
            + 'LEFT JOIN EmployeeCompany AS EmployeeCompany' 
            + CAST(@currentCount AS VARCHAR(2)) 
            + ' ON EmployeeCompany0.EmployeeID = EmployeeCompany' 
            + CAST(@currentCount AS VARCHAR(2)) + '.EmployeeID'
    end

    set @currentCount = @currentCount + 1
end
set @createtemptable = @createtemptable + ')'
--This next line can be whatever you need it to be
set @filltablejoin = @filltablejoin + 'WHERE employeeCompany0.EmployeeID = 1'

exec @createtemptable 
exec @filltableselect + @filltablejoin

n to n relationship how to obtain a result in one row

2 Answers2