Concatenate lines with the same code

Question

When executing the query

select * from ivr_interacao,ivr_business where ivr_interacao.codigo = ivr_business.idvisita and ivr_business.campanha = 5 order by start_time asc

I have the following answer:

CHAMADO ID VISITA   NOME    TELEFONE        PERCURSO    DATA
7193751 799445  MARIA BATISTA   11111                   27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111                   27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111                   27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111       PLAYDATA    27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111       PLAYPERIODO 27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111       MENU        27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111       MENU        27/06/2018 11:03
7193751 799445  MARIA BATISTA   11111                   27/06/2018 11:04

However I wanted it to be in this format:

CHAMADO ID VISITA   NOME    TELEFONE        PERCURSO                              DATA
7193751 799445  MARIA BATISTA   11111 '','',PLAYDATA,PLAYPERIODO,MENU,MENU,'' 27/06/2018 11:03

but I am not able to concatenate, I tried using the following query:

SELECT  codigo,
    nomecliente,acao,
COALESCE(
    (SELECT CAST(nomecliente AS VARCHAR(30)) + ';' AS [text()]
     FROM ivr_interacao AS O
     WHERE O.codigo  = C.idvisita

     ORDER BY nomecliente
     FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS Produtos
FROM ivr_business AS C
GROUP BY codigo,nomecliente;

Any suggestions?

Answer 1

Only if the database is Postgresql

Mysql you can use group_concat

select ivr_interacao.CHAMADO, 
       ivr_interacao.ID, 
       ivr_interacao.VISITA,
       ivr_interacao.NOME,
       ivr_interacao.TELEFONE,
       array_to_string(array_agg(ivr_business.PERCURSO), ', ') as PERCURSO,
       min(ivr_business.DATA) as data
  from ivr_interacao, ivr_business
 where ivr_interacao.codigo = ivr_business.idvisita 
   and ivr_business.campanha = 5 
 group by ivr_interacao.CHAMADO, 
          ivr_interacao.ID, 
          ivr_interacao.VISITA,
          ivr_interacao.NOME,
          ivr_interacao.TELEFONE;

I’m just speculating on the name of the tables, this may vary.