Get distinct, sorted list from column of lists in SQL?-CodePudding

I have a SQL table that looks like the following:

Field1,Field2
AAAA,111;222
AAAA,222;333
AAAA,333;444
BBBB,999;000

How can I make this one row only as a list of ascending values?

Field1,Field2
AAAA,111;222;333;444
BBBB,000;999

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (field1, field2) as
  2  (select 'AAAA', '111;222' from dual union all
  3   select 'AAAA', '222;333' from dual union all
  4   select 'AAAA', '333;444' from dual union all
  5   select 'BBBB', '999;000' from dual
  6  ),

First split field2 into rows; distinct will eliminate duplicates; then aggregate them back using listagg:

  7  temp as
  8    (select distinct
  9       field1,
 10       regexp_substr(field2, '[^;] ', 1, column_value) f2
 11     from test cross join
 12       table(cast(multiset(select level from dual
 13                           connect by level <= regexp_count(field2, ';')   1
 14                          ) as sys.odcinumberlist))
 15    )
 16  select field1,
 17         listagg(f2, ';') within group (order by f2) field2
 18  from temp
 19  group by field1;

FIEL FIELD2
---- ------------------------------
AAAA 111;222;333;444
BBBB 000;999

SQL>

CodePudding user response：

You can do it with simple (fast) string functions (rather than slow regular expressions) using:

WITH split_fields (field1, field2, spos, epos) AS (
  SELECT field1, field2, 1, INSTR(field2, ';', 1)
  FROM   table_name
UNION ALL
  SELECT field1, field2, epos   1, INSTR(field2, ';', epos   1)
  FROM   split_fields
  WHERE  epos > 0
),
items (field1, item) AS (
  SELECT DISTINCT
         field1,
         CASE epos
         WHEN 0
         THEN SUBSTR(field2, spos)
         ELSE SUBSTR(field2, spos, epos - spos)
         END
  FROM   split_fields
)
SELECT field1,
       LISTAGG(item, ';') WITHIN GROUP (ORDER BY item) AS field2
FROM   items
GROUP BY field1

Which, for the sample data:

CREATE TABLE table_name (Field1, Field2) AS
SELECT 'AAAA', '111;222' FROM DUAL UNION ALL
SELECT 'AAAA', '222;333' FROM DUAL UNION ALL
SELECT 'AAAA', '333;444' FROM DUAL UNION ALL
SELECT 'BBBB', '999;000' FROM DUAL;

Outputs:

FIELD1 FIELD2

AAAA 111;222;333;444

BBBB 000;999

FIELD1	FIELD2
AAAA	111;222;333;444
BBBB	000;999

db<>fiddle here

CodePudding user response：

You can use regular expressions to break apart the FIELD2 values, then put them back together with LISTAGG:

WITH cteSubfields
  AS (SELECT DISTINCT FIELD1, REGEXP_SUBSTR(FIELD2, '[^;] ', 1, LEVEL) AS SUBFIELD
        FROM TEST_TABLE
        CONNECT BY LEVEL <= REGEXP_COUNT(FIELD2, ';') 1)
SELECT DISTINCT FIELD1,
                LISTAGG(SUBFIELD, ',') OVER (PARTITION BY FIELD1) AS FIELD2
  FROM cteSubfields

db<>fiddle here