Split oracle column value into comma separated values

Question

I have a table with column having value 5 in oracle I want to change the value of column to array of values like [5,4,3,2,1] means values with difference of 1

Answer 1

This is a direct application of "hierarchical queries" (see the Oracle documentation if not familiar). It uses sys_connect_by_path to create the list of values (the square brackets are added outside the hierarchical query - and the leading comma, which sys_connect_by_path generates, needs to be chopped off first, with ltrim()).

with
     test_data ( id, val ) as (
       select 101, 5 from dual union all
       select 103, 3 from dual
     )
-- End of made-up test data; NOT part of the query.
-- SQL query begins BELOW THIS LINE. Change table and column names as needed.
select id, val,
       '[' || ltrim(sys_connect_by_path(val - level + 1, ','), ',') || ']' as val_list
from   test_data
where  connect_by_isleaf = 1
connect by level <= val
       and prior id = id
       and prior sys_guid() is not null
;

 ID  VAL  VAL_LIST
---  ---  -----------
101    5  [5,4,3,2,1]
103    3  [3,2,1]

Answer 2

For me the easiest way is create function for that

CREATE OR REPLACE FUNCTION toArray (p_value NUMBER)
   RETURN VARCHAR
AS
   l_result   VARCHAR (4000) := '[';
BEGIN
   FOR i IN REVERSE 2 .. p_value
   LOOP
      l_result := l_result || i || ',';
   END LOOP;

   IF (p_value >= 1)
   THEN
      l_result := l_result || 1;
   END IF;

   l_result := l_result || ']';
   RETURN l_result;
END;

Query select toArray(5) from dual return result [5,4,3,2,1]

Answer 3

I would suggest a bit simpler approach to use. Hope below snippet helps. Only drawback here what i can suggest is LISTAGG works only oracle version 11 and above. So it wont work in below versions.

SELECT listagg(a.lvl, ',') within GROUP (
ORDER BY a.lvl DESC) somedata
from
  (SELECT '&Enter_val'+1 - level lvl,
    1 lvl2
  from dual
    CONNECT BY level < '&Enter_val'+1
  ORDER BY lvl DESC
  )a;