Sorting comma delimited datasets in row

Question

This is what is given

Numbers         Powers
4,5,1           WATER,FIRE
6,3,9           ICE,WATER,FIRE

My requirement is (sorted order)

Numbers               Powers
1,4,5                 FIRE,WATER
3,6,9                 FIRE,ICE,WATER .

I want it in sorted order! How to do it in database?

Start with [Splitting string into multiple rows in Oracle](https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle). And stop there too. Once you get your CSV data onto separate records, _don't_ roll it up again into CSV. — Tim Biegeleisen, Jul 03 '19 at 13:04
DB2 or Oracle? Those are two very different database products — , Jul 03 '19 at 13:10
Why on earth are you storing comma separated values in a single column? That is a really, really bad idea — , Jul 03 '19 at 13:10

score 2 · Answer 1 · answered Jul 03 '19 at 13:09

Split column to rows, then aggregate them back, sorted.

SQL> with test (id, num, pow) as
  2    (select 1, '4,5,1', 'water,fire'     from dual union all
  3     select 2, '6,3,9', 'ice,water,fire' from dual
  4    ),
  5  temp as
  6    -- split columns to rows
  7    (select id,
  8            regexp_substr(num, '[^,]+', 1, column_value) num1,
  9            regexp_substr(pow, '[^,]+', 1, column_value) pow1
 10     from test join table(cast(multiset(select level from dual
 11                                        connect by level <= regexp_count(num, ',') + 1
 12                                       ) as sys.odcinumberlist)) on 1 = 1
 13    )
 14    -- aggregate them back, sorted
 15  select id,
 16         listagg(num1, ',') within group (order by to_number(num1)) num_result,
 17         listagg(pow1, ',') within group (order by pow1) pow_result
 18  from temp
 19  group by id;

        ID NUM_RESULT                     POW_RESULT
---------- ------------------------------ ------------------------------
         1 1,4,5                          fire,water
         2 3,6,9                          fire,ice,water

SQL>

If there are more values in POW than in NUM then this solution is not working. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8895ea42a48d57ed6e8158f9514cbd18) — Popeye, Jul 03 '19 at 14:08

Popeye · Answer 2 · 2019-07-04T06:33:57.953

You can try the following:

I have used the table as I will need some value to get a distinct value. here I have used ROWID.

SELECT
    ID,
    LISTAGG(NUM, ',') WITHIN GROUP(
        ORDER BY
            NUM
    ) AS NUM,
    LISTAGG(POW, ',') WITHIN GROUP(
        ORDER BY
            POW
    ) AS POW
FROM
    (
        SELECT
            DISTINCT ROWID,
            ID,
            REGEXP_SUBSTR(NUM, '[^,]+', 1, LEVEL) NUM,
            REGEXP_SUBSTR(POW, '[^,]+', 1, LEVEL) POW
        FROM
            TEST
        CONNECT BY REGEXP_SUBSTR(NUM, '[^,]+', 1, LEVEL) IS NOT NULL
                   OR REGEXP_SUBSTR(POW, '[^,]+', 1, LEVEL) IS NOT NULL
    )
    GROUP BY ID
    ORDER BY ID;

db<>fiddle demo

Cheers!!

---- UPDATE ----

As mentioned in a comment that it is generating duplicates, I have re-framed the whole query as following:

SELECT
    ID,
    LISTAGG(C_S.NUM, ',') WITHIN GROUP(
                ORDER BY
                    C_S.NUM
            ) AS NUM,
    LISTAGG(C_S.POW, ',') WITHIN GROUP(
                ORDER BY
                    C_S.POW
            ) AS POW
FROM
(SELECT
    T.ID,
    REGEXP_SUBSTR(T.NUM, '[^,]+', 1, NUMS_COMMA.COLUMN_VALUE) NUM,
    REGEXP_SUBSTR(T.POW, '[^,]+', 1, NUMS_COMMA.COLUMN_VALUE) POW
FROM
    TEST T,
    TABLE ( CAST(MULTISET(
        SELECT
            LEVEL
        FROM
            DUAL
        CONNECT BY
            LEVEL <= GREATEST(LENGTH(REGEXP_REPLACE(T.NUM, '[^,]+')),
            LENGTH(REGEXP_REPLACE(T.POW, '[^,]+'))) + 1
    ) AS SYS.ODCINUMBERLIST) ) NUMS_COMMA) C_S
 GROUP BY ID;

db<>fiddle demo updated

Cheers!!

This is going to generate exponentially many rows from the `CONNECT BY` as it is going to cross-correlate between rows. Yes, the `DISTINCT` clause will get rid of the duplicates but its very inefficient. Remove the `DISTINCT` clause and you'll see just how many duplicates get generated and that need to be removed [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=1227e15737d0087ee5abbcaa6c2254a3). — MT0, Jul 03 '19 at 21:29
@MT0 , Yes it was generating duplicates. Thanks for the valuable review. I have changed the query now. Can you please review? I think now it is perfect. — Popeye, Jul 04 '19 at 05:14

score 1 · Answer 3 · answered Jul 03 '19 at 21:17

Oracle Setup:

CREATE TABLE test_data ( Numbers, Powers ) AS
SELECT '4,5,1', 'WATER,FIRE'     FROM DUAL UNION ALL
SELECT '6,3,9', 'ICE,WATER,FIRE' FROM DUAL UNION ALL
SELECT '7',     'D,B,E,C,A'      FROM DUAL

Query:

SELECT (
         SELECT LISTAGG( TO_NUMBER( REGEXP_SUBSTR( t.numbers, '\d+', 1, LEVEL ) ), ',' )
                  WITHIN GROUP ( ORDER BY TO_NUMBER( REGEXP_SUBSTR( t.numbers, '\d+', 1, LEVEL ) ) )
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT( t.numbers, ',' ) + 1
       ) AS numbers,
       (
         SELECT LISTAGG( REGEXP_SUBSTR( t.powers, '[^,]+', 1, LEVEL ), ',' )
                  WITHIN GROUP ( ORDER BY REGEXP_SUBSTR( t.powers, '[^,]+', 1, LEVEL ) )
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT( t.powers, ',' ) + 1
       ) AS numbers
FROM   test_data t

Output:

NUMBERS | NUMBERS       
:------ | :-------------
1,4,5   | FIRE,WATER    
3,6,9   | FIRE,ICE,WATER
7       | A,B,C,D,E

db<>fiddle here

Sorting comma delimited datasets in row

3 Answers3