INNER JOIN Horizontal Results

Question

I am sure it is something simple, but I am not succeeding: I will give the example.

tb_empresa e tb_contatosdaempresa

Each company has up to 8 contacts (Fields: id_company / name / phone / email)

I need to make an Inner Join in a way that for each company returned (tb_company) appear all the contacts and their respective data, but vertically, in columns, to then export to Excel !!! Type:

Line01 >> Company Name | Name Contact 01 | Phone Contact 01 | Email Contact 01 | Name Contact 02 | Phone Contact 02 | Email Contact 02...

Line02 >> Company Name | Name Contact 01 | Phone Contact 01 | Email Contact 01 | Name Contact 02 | Phone Contact 02 | Email Contact 02... Thus

Line03 >> Company Name | Name Contact 01 | Phone Contact 01 | Email Contact 01 | Name Contact 02 | Phone Contact 02 | Email Contact 02... Thus

So consectively until the eighth contact, even if you do not have 8 contacts registered for this company (In the case is blank). Because the way you’ve been doing, a line is created for each contact the company has, repeating the name of the company several times.

Can you give me a hand ???

Answer 1

You can make the select so:

 SELECT e.*,
( 
  SELECT ',' + ContatoNome AS 'data()' 
         FROM #Contato where idEmpresa = e.Id FOR XML PATH('')
) as contatos
FROM #Empresa as e

Utilize this site to test.

EDITED
I was taking a look at the documentation of the pivot function but I don’t think it will suit you either.
As your goal is to export to Excel I imagine you would be exporting the result of this select to a certain CSV file?
If that’s the idea to solve your problem it would be to create a select bringing everything in a single column and each already separated by comma (or the charactere you find best).
Follow the example below:

select ' NomeEmpresa,Contato1,emailcontato1,Contato2,emailcontato2' as 'ignorar' union all
 select e.EmpresaNome+','+
( 
  SELECT ContatoNome +','+ContatoEmail+',' AS 'data()' 
         FROM #Contato where idEmpresa = e.Id FOR XML PATH('')
         )
from #Empresa as e

The important thing here is the first select that will contain the header of your fields and then the table data.
After generating the CSV file, open it in excel, delete the first line with the name 'ignore' and ready.
Test the example above on this website here you can export to CSV as well.