How to concatenate the results of a RELATION into a SELECT?

Question

Having the tables PRODUTO , CATEGORIA whereas a CATEGORY may have several PRODUCTS, would like a select list in a column nvarchar all the PRODUCT NAME of a CATEGORY.

Something like:

       select concat( select p.nome from categoria c 
       inner join produto p on c.id = p.fk_categoria 
       where c.id = categoria.id) as ProdutosDaCategoria from categoria

This code won’t work but it’s an attempt to explain what I need.

You would also have to put a "," or other separator character between each result of subquery.

Someone knows how to do ?

Note: any solution that brings all the categories listed and the "name" of products concatenates in an nvarchar meets the problem (as long as the query is not unviable expensive ) .

Answer 1

Here’s a solution using FOR XML with PATH mode

SELECT DISTINCT C.ID, 
       SUBSTRING((
          SELECT ', ' + P.NOME
          FROM dbo.Produtos P
          WHERE P.FK_CATEGORIA = C.ID
          FOR XML PATH ('')
        ), 2, 1000) TODOS_OS_PRODUTOS
FROM dbo.Categorias C
ORDER BY C.ID

Stay here the Sqlfiddle

Answer 2

without the database structure gets a little complicated to help you, but anyway try to accomplish this way.

select processo.numero , Concat(descricao_parcela.nome,', ', outro_campo) as objetosDaAcao 
  from processo
  left join parcela 
    on >>>> veja as ligações necessarias <<<<
  left join descricao_parcela 
    on descricao_parcela.id = parcela.fk_descricao_parcela 
 where processo.id = parcela.fk_processo 
 order by processo.id 

in the Concat you define the form and the fields you will need if they will be separated by point, comma, quotes and so on, just pass the function with single quotes and separate by comma all fields and characters that will be added.

See the link to the parcel table in more case it doesn’t work send a message that I help you !! Hug...