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'm not sure how 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?