Group By inside Rtrim(Xmlagg (Xmlelement (e,element || ',')).extract ( '//text()' ).GetClobVal(), ',')

Question

I need to group values ​​inside a query using (or not) the command Rtrim(Xmlagg (Xmlelement (e,column || ',')).extract ( '//text()' ).GetClobVal(), ','), but I can't find any literature where explain a way to group the data inside this command. The code is very simple, as you can see below:

SELECT ID,
Rtrim(Xmlagg (Xmlelement (and, CONTRACTS || ',')).extract ( '//text()' ).GetClobVal(), ',') AS CONTRACTS
FROM TABLE_A
GROUP BY ID

The result in CONTRACTS is always repeated when the ID is found, thats ok, it´s working!

ID	CONTRACTS
876	1,1,1,2,3,3

But what I really need is this return:

ID	CONTRACTS
876	1,2,3

It´s not necessary to use the command Rtrim(Xmlagg (Xmlelement (e,column || ',')).extract ( '//text()' ).GetClobVal(), ','), instead, I just use to concatenate element with comma "," in the same column.

If anyone can help me, I would be very grateful!

Answer 1

If your values will fit into a VARCHAR2 data type (rather than a CLOB) then you can use a nested sub-query to get the DISTINCT values for each ID:

SELECT ID,
       LISTAGG(contracts, ',') WITHIN GROUP (ORDER BY contracts) AS CONTRACTS
FROM   ( SELECT DISTINCT id, contracts FROM TABLE_A)
GROUP BY ID

Or, from Oracle 19c, it is built-in to LISTAGG:

SELECT ID,
       LISTAGG(DISTINCT contracts, ',') WITHIN GROUP (ORDER BY contracts) AS CONTRACTS
FROM   TABLE_A
GROUP BY ID

If you want a CLOB then you can use the same technique as the first query:

SELECT ID,
       Rtrim(
         Xmlagg(
           Xmlelement(name, CONTRACTS || ',')
           ORDER BY contracts
         ).extract ( '//text()' ).GetClobVal(),
         ','
       ) AS CONTRACTS
FROM   (SELECT DISTINCT id, contracts FROM TABLE_A)
GROUP BY ID

Which, for the sample data:

CREATE TABLE table_a (id, contracts) AS
SELECT 876, 1 FROM DUAL UNION ALL
SELECT 876, 1 FROM DUAL UNION ALL
SELECT 876, 1 FROM DUAL UNION ALL
SELECT 876, 2 FROM DUAL UNION ALL
SELECT 876, 2 FROM DUAL UNION ALL
SELECT 876, 3 FROM DUAL UNION ALL
SELECT 876, 3 FROM DUAL UNION ALL
SELECT 876, 3 FROM DUAL;

All output:

ID	CONTRACTS
876	1,2,3

db<>fiddle here

Answer 2

It's much easier to do all those operation in XML functions: DBFiddle

SELECT--+ NO_XML_QUERY_REWRITE
  id,
  xmlquery(
    'string-join(distinct-values($R/R/X/text()),",")'
    passing 
        Xmlelement(
            R,
            Xmlagg(
                Xmlelement (X, CONTRACTS)
                order by CONTRACTS
            )) as R
    RETURNING CONTENT
  ) AS CONTRACTS
FROM TABLE_A
GROUP BY ID;

Full example with test data:

with table_a (id, contracts) AS (
SELECT 876, 1 FROM DUAL UNION ALL
SELECT 876, 1 FROM DUAL UNION ALL
SELECT 876, 1 FROM DUAL UNION ALL
SELECT 876, 2 FROM DUAL UNION ALL
SELECT 876, 2 FROM DUAL UNION ALL
SELECT 876, 3 FROM DUAL UNION ALL
SELECT 876, 3 FROM DUAL UNION ALL
SELECT 876, 3 FROM DUAL
)
SELECT--+ NO_XML_QUERY_REWRITE
  id,
  xmlquery(
    'string-join(distinct-values($R/R/X/text()),",")'
    passing 
        Xmlelement(
            R,
            Xmlagg(
                Xmlelement (X, CONTRACTS)
                order by CONTRACTS
            )) as R
    RETURNING CONTENT
  ) AS CONTRACTS
FROM TABLE_A
GROUP BY ID;