Concatenate values of a Database into SQL

Question

Using SQL I am making a record that I will give a SELECT in a database in which some records comes duplicated which is structured as follows with the same adhesion ex:

+------------+-------------+-------------+
|     ADE    | COLUMN_NAME | IS_IDENTITY |
+------------+-------------+-------------+
|    caio    |      3      |      10     |
|    caio    |      4      |       4     |
|    caio    |      4      |       8     |
+------------+-------------+-------------+

and I want to group the data as follows in the same cell, in which the divergence will be concatenated with the Idmotivo separated by " - " and the reasons for the divergence by "," and for the grouping of divergences by "|" being as follows ex:

+------------+-------------+-------------+
|     ADE    |   DIVERGÊNCIA - MOTIVO    |
+------------+-------------+-------------+
|    caio    |       3-10 | 4-4, 8       |     
+------------+-------------+-------------+

I tried to use GROUP and PIVOT together, but I don’t know how to use it well.

Answer 1

In postgresql is easy to do using with.

Given the table:

|nome           |divergencia    |motivo         |
|---------------|---------------|---------------|
|caio           |3              |10             |
|caio           |4              |4              |
|caio           |4              |8              |
|maria          |3              |5              |
|maria          |3              |10             |

That consultation:

with tb_motivos as (
    select nome, divergencia, string_agg(motivo, ',') as motivo
    from ade
    group by nome, divergencia
    order by motivo
) 
select nome, string_agg(divergencia || ' - ' || motivo, '|') as div_mot 
from tb_motivos
group by nome
order by nome, div_mot

Will bring about:

|nome   |div_mot      |
|-------|-------------|
|caio   |3-10|4-4,8   |
|maria  |3-5,10       |