How to concatenate the results of a RELATIONSHIP in a SELECT?

Navigation
2

Since the tables PRODUTO , CATEGORIA and a CATEGORY can have several PRODUCTS , I'd like a select to be listed in a nvarchar column All 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

Thiscodewillnotworkbutit'sanattempttoexplainwhatIneed.

Youwouldalsohavetoputa"," or other separator character between each result of subquery .

Does anyone know how to do it?

Note: Any solution that brings all the categories listed and the "name" of the products concatenated in nvarchar addresses the problem (as long as the query does not look unfeasible ).

    
asked by anonymous 11.02.2016 / 18:31

2 answers

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 SQLFiddle

    
12.02.2016 / 00:06
0

Without the database structure it gets a little tricky to help you, but try anyway. 

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 concat vc define the form and the fields that you will need if they are separated by dot, comma, quotation marks and so on, just pass the function with single quotation marks and separate by comma all the fields and characters to be added.

See the correga link for the parcel table in case it does not work, send me a message that will help you !! Hugs ...

    
11.02.2016 / 23:51
FullCalendar fill in events by JSON Google Directions Service does not count requests made with my api key