How can I get comma separated values from a table in a single cell in Oracle SQL? How do I do it?

For example, if the input table I have is the following::

My idea would be to achieve a grouping like the one below:

As you can see in this case I have 2 different ids, when I group by id I would like the missing dates to have a null value and for the first value to correspond to the first date for that id. Also, when there are no values on that date, add a null value.

How can I put those null values in the correct place? How do I detect the absence of these values and set them as null? How to make the positions of the values correlate with the dates?

I've been trying to use the listgg or xmlagg function to group, but at the moment I don't know how to cover the missing places.

CodePudding user response：

Another option; read comments within code. Sample data in lines #1 - 9; query begins at line #10.

SQL> with test(id, value, datum) as
  2    (select 1, 5, date '2021-01-10' from dual union all  --> missing February and March
  3     select 1, 8, date '2021-04-13' from dual union all
  4     select 1, 3, date '2021-05-22' from dual union all
  5     --
  6     select 2, 1, date '2021-03-21' from dual union all
  7     select 2, 7, date '2021-04-22' from dual union all  --> missing May and June
  8     select 2, 9, date '2021-07-10' from dual
  9    ),

 10  -- calendar per ID
 11  minimax as
 12    (select id, trunc(min(datum), 'mm') mindat, trunc(max(datum), 'mm') maxdat
 13     from test
 14     group by id
 15    ),
 16  calendar as
 17    (select m.id,
 18            'null' value,
 19            add_months(m.mindat, column_value - 1) datum
 20     from minimax m
 21     cross join table(cast(multiset(select level from dual
 22                                    connect by level <= ceil(months_between(maxdat, mindat))   1
 23                                   ) as sys.odcinumberlist))
 24    )
 25  select c.id,
 26    listagg(nvl(to_char(t.value), c.value), ', ') within group (order by c.datum) result
 27  from calendar c left join test t on t.id = c.id and trunc(t.datum, 'mm') = c.datum
 28  group by c.id;

        ID RESULT
---------- ----------------------------------------
         1 5, null, null, 8, 3
         2 1, 7, null, null, 9

SQL>

CodePudding user response：

Use a PARTITIONed OUTER JOIN:

WITH calendar (day) AS (
  SELECT DATE '2021-01-18' FROM DUAL UNION ALL
  SELECT DATE '2021-02-12' FROM DUAL UNION ALL
  SELECT DATE '2021-03-16' FROM DUAL UNION ALL
  SELECT DATE '2021-04-15' FROM DUAL UNION ALL
  SELECT DATE '2021-05-14' FROM DUAL UNION ALL
  SELECT DATE '2021-06-14' FROM DUAL UNION ALL
  SELECT DATE '2021-07-14' FROM DUAL UNION ALL
  SELECT DATE '2021-08-13' FROM DUAL UNION ALL
  SELECT DATE '2021-09-14' FROM DUAL UNION ALL
  SELECT DATE '2021-10-15' FROM DUAL
  -- Or
  -- SELECT DISTINCT datetime FROM table_name
)
SELECT t.id,
       LISTAGG(COALESCE(TO_CHAR(t.value), 'null'), ',')
         WITHIN GROUP (ORDER BY c.day)
         AS grouped_values
FROM   calendar c
       LEFT OUTER JOIN table_name t
       PARTITION BY (t.id)
       ON (c.day = t.datetime)
GROUP BY t.id

Or:

WITH calendar (day) AS (
  SELECT ADD_MONTHS(DATE '2021-01-01', LEVEL - 1)
  FROM   DUAL
  CONNECT BY LEVEL <= 10
  -- or
  -- SELECT ADD_MONTHS(min_dt, LEVEL - 1)
  -- FROM   (
  --   SELECT MIN(TRUNC(datetime, 'MM')) AS min_dt,
  --          MAX(TRUNC(datetime, 'MM')) AS max_dt
  --   FROM   table_name
  -- )
  -- CONNECT BY ADD_MONTHS(min_dt, LEVEL - 1) <= max_dt
)
SELECT t.id,
       LISTAGG(COALESCE(TO_CHAR(t.value), 'null'), ',') WITHIN GROUP (ORDER BY c.day)
         AS grouped_values
FROM   calendar c
       LEFT OUTER JOIN table_name t
       PARTITION BY (t.id)
       ON (c.day = TRUNC(t.datetime, 'MM'))
GROUP BY t.id

Which, for the sample data:

CREATE TABLE table_name (id, value, datetime) AS
  SELECT 9245, 137, DATE '2021-01-18' FROM DUAL UNION ALL
  SELECT 9245, 125, DATE '2021-02-12' FROM DUAL UNION ALL
  SELECT 9245, 131, DATE '2021-03-16' FROM DUAL UNION ALL
  SELECT 9245, 100, DATE '2021-04-15' FROM DUAL UNION ALL
  SELECT 9245,  62, DATE '2021-05-14' FROM DUAL UNION ALL
  SELECT 9245,  41, DATE '2021-08-13' FROM DUAL UNION ALL
  SELECT 9245,  42, DATE '2021-09-14' FROM DUAL UNION ALL
  SELECT 9245,  44, DATE '2021-10-15' FROM DUAL UNION ALL
  SELECT 8873, 191, DATE '2021-01-18' FROM DUAL UNION ALL
  SELECT 8873, 157, DATE '2021-02-12' FROM DUAL UNION ALL
  SELECT 8873, 189, DATE '2021-03-16' FROM DUAL UNION ALL
  SELECT 8873, 216, DATE '2021-04-15' FROM DUAL UNION ALL
  SELECT 8873, 275, DATE '2021-05-14' FROM DUAL UNION ALL
  SELECT 8873, 381, DATE '2021-06-14' FROM DUAL UNION ALL
  SELECT 8873, 411, DATE '2021-07-14' FROM DUAL UNION ALL
  SELECT 8873, 373, DATE '2021-08-13' FROM DUAL UNION ALL
  SELECT 8873, 373, DATE '2021-09-14' FROM DUAL UNION ALL
  SELECT 8873, 358, DATE '2021-10-15' FROM DUAL;

Both output:

ID	GROUPED_VALUES
8873	191,157,189,216,275,381,411,373,373,358
9245	137,125,131,100,62,null,null,41,42,44

db<>fiddle here