Group values of different columns into one, comma-separated

Asked

Viewed 3,140 times

1

I got the following select:

SELECT DISTINCT ORGAO, UNIDADE FROM TESTE

Where the following result is generated: 

Órgao Unidade
1     2
1     3
1     4
1     5

Four records of the same organ are returned, but with different units. What I wanted was to return only one row with 2 columns, the first organ (1) and the second Unit (2,3,4,5) separated by commas.

How can I do that? Thanks in advance.

sql oracle

1 answer

1


What you are trying to do doesn’t seem like a very good idea, and usually when you go down that road, it is a sign that there is something wrong in your understanding of the purpose of the query or the result generated.

However, assuming that is exactly what you want. You could use the LISTAGG of the oracle in this way:

SELECT DISTINCT
    orgao,
    LISTAGG(unidade, ',') WITHIN GROUP (ORDER BY unidade) AS unidades
FROM TESTE
GROUP BY orgao;

Source: https://oracle-base.com/articles/misc/string-aggregation-techniques

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.