[ad_1]
From Oracle 12, you can use MATCH_RECOGNIZE
to perform row-by-row comparisons and aggregate the adjacent duplicates and then you can use LISTAGG
to aggregate the unique values:
SELECT col_key,
LISTAGG(value, ' | ') WITHIN GROUP (ORDER BY mno) AS col_value
FROM (SELECT t.*,
ROWNUM AS rn -- You need to provide a way of getting this order!
FROM table1 t)
MATCH_RECOGNIZE(
PARTITION BY col_key
ORDER BY rn
MEASURES
MATCH_NUMBER() AS mno,
FIRST(col_value) AS value
PATTERN (same_value+)
DEFINE
same_value AS FIRST(col_value) = col_value
)
GROUP BY col_key;
Which, for the sample data:
CREATE TABLE Table1 (col_key, col_value) AS
SELECT 'key1', 'value1' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value1' FROM DUAL UNION ALL
SELECT 'key1', 'value1' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL;
Outputs:
COL_KEY COL_VALUE key1 value1 | value2 | value3 | value2 | value1 | value3 | value2
db<>fiddle here
[ad_2]