eliminate values that are next to each other in SQL

January 20, 2022

How do I use the listagg function in SQL Oracle to eliminate values that are next to each other? Data can be repeated as long as it is not next to each other.

Example:

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',')
  FROM tablename;

But I have value:

apple, apple, apple, apple, apple, apple, apple, apple, apple, apple, apple, apple, pear, apple, orange, orange, orange, orange, orange, grape, grape, apple, grape

How to arrange sql to get such values (repeating but not next to each other).
Excpect:

apple, pear, apple, orange, grape, apple, grape

Any idea?

>Solution :

You can use:

SELECT LISTAGG(colname, ',') WITHIN GROUP (ORDER BY ROWNUM)
         AS items
FROM   (
  SELECT colname,
         LAG(colname) OVER (ORDER BY ROWNUM) AS prev_colname,
         ROWNUM AS rn
  FROM   tablename
)
WHERE rn = 1
OR    prev_colname <> colname

Or, from Oracle 12:

SELECT LISTAGG(colname, ',') WITHIN GROUP (ORDER BY ROWNUM)
         AS items
FROM   (
  SELECT colname,
         ROWNUM AS rn
  FROM   tablename
)
MATCH_RECOGNIZE(
  ORDER BY rn
  MEASURES
    FIRST(colname) AS colname
  PATTERN (same+)
  DEFINE same AS FIRST(colname) = colname
);

(Note: ROWNUM should be replaced by a column that will identify a deterministic ordering in the rows such as a timestamp or an id column; however, such a column is not present in your sample data so it cannot be used. ROWNUM will just number the rows in the order that the SQL engine processes them and that processing order may be non-deterministic.)

Which, for the sample data:

CREATE TABLE tablename (colname) AS
SELECT 'apple'  FROM DUAL CONNECT BY LEVEL <= 8 UNION ALL
SELECT 'pear'   FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'apple'  FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'orange' FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT 'grape'  FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 'apple'  FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'grape'  FROM DUAL CONNECT BY LEVEL <= 1;

Both output: