Concatenate varchar2 PLSQL

Question

I have 2 tables with relationship 1 to many and I need to concatenate all the values in the column Nome table 2 in only one select column.

Ex.:

In the example, the return of what I need would be Maria, João, José

Note: The database is PLSQL.

Answer 1

Jonathan I found a cool example of this site https://social.msdn.microsoft.com/Forums/sqlserver/pt-BR/6177bd7a-e2fc-46f4-9646-8fd1480cf14b/concatenar-valores-de-linhas-em-uma-coluna?forum=520

I have the following table:

Code Customer Product 1 Jorge floor 1 Jorge door 1 Jorge faucet

I need the result of this select to stay like this:

Code Customer Product 1 Jorge floor;door;tap

-- Concatenating

SELECT  CODIGO,
        CLIENTE,
    COALESCE(
        (SELECT CAST(PRODUTO AS VARCHAR(10)) + ';' AS [text()]
         FROM TABELA AS O
         WHERE O.CODIGO  = C.CODIGO
         and   O.CLIENTE = C.CLIENTE
         ORDER BY CODIGO
         FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS Produtos
FROM TABELA AS C
GROUP BY CODIGO,CLIENTE;

Answer 2

You can group and use a listgg, for example:

select t1.ID , listagg(t2.nome, ',') within group( order by t2.nome) as nome
from tabela1 t1
left join tabela2 t2 on t1.id_t2 = t2.id
group by t1.ID
;

Only answering that the id would have to be more or less like this

John, Mary and Joseph would have to have the same id