I want to group database data (Firebird) to send emails

Question

Good, I am selecting the data for sending the email in this SELECT:

SELECT V.id_venda_cab,
V.id_cliente,
V.Total, 
C.nome,
i.id_produto, 
p.descricao,
C.email
FROM VENDA_CAB V INNER JOIN CLIENTE C ON C.ID_CLIENTE = V.ID_CLIENTE
INNER JOIN venda_item i ON i.id_venda_cab = v.id_venda_cab
inner join produto p on p.id_produto = i.id_produto
WHERE V.faturado = 'N'
ORDER BY V.ID_VENDA_CAB

And He returns:

I want to group the data of the same id_sale, where customers are equal, but the products are other.

Answer 1

I got the desired result using:

select CODIGO,
LIST(DESCRICAO,',') as DESCRICRAO,
LIST(distinct(NOME)) as Nome,
LIST(distinct(EMAIL)) as Email,
List(distinct(Tot)) as Total FROM(SELECT V.id_venda_cab AS CODIGO,
V.Total AS TOT,
C.nome AS NOME,
p.descricao AS DESCRICAO,
C.email AS EMAIL
FROM VENDA_CAB V INNER JOIN CLIENTE C ON C.ID_CLIENTE = V.ID_CLIENTE
INNER JOIN venda_item i ON i.id_venda_cab = v.id_venda_cab
inner join produto p on p.id_produto = i.id_produto
WHERE V.faturado = 'N'
ORDER BY V.id_venda_cab)
GROUP BY CODIGO

I used list() and distinct() to distinguish repeated data.