CODEWITHSUNDEEP
sqloracle11ggroupinglistagg
Alexander Martins
Alexander Martins

Reputation: 383

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

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::

id value datetime
9245 44 2021-10-15 00:00:00
9245 42 2021-09-14 00:00:00
9245 41 2021-08-13 00:00:00
9245 62 2021-05-14 00:00:00
9245 100 2021-04-15 00:00:00
9245 131 2021-03-16 00:00:00
9245 125 2021-02-12 00:00:00
9245 137 2021-01-18 00:00:00
8873 358 2021-10-15 00:00:00
8873 373 2021-09-14 00:00:00
8873 373 2021-08-13 00:00:00
8873 411 2021-07-14 00:00:00
8873 381 2021-06-14 00:00:00
8873 275 2021-05-14 00:00:00
8873 216 2021-04-15 00:00:00
8873 189 2021-03-16 00:00:00
8873 157 2021-02-12 00:00:00
8873 191 2021-01-18 00:00:00

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

id grouped_values
8873 191,157,Null,Null,Null,381,411,373,373,358
9245 137,125,131,100,62,Null,Null,41,42,44

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.

Upvotes: 0

Views: 958

Answers (3)

Alexander Martins
Alexander Martins

Reputation: 383

You can run this query directly without creating any tables. Here is a version with start date and end date with parameters:

SELECT
    FE.id
    ,LISTAGG(NVL(TO_CHAR(TRUNC(CON.value)), 'null'), ',') WITHIN GROUP (ORDER BY FE.the_date ASC) GROUPED_VALUES
FROM
    (--begin from1
        SELECT id
            ,EXTRACT (YEAR FROM the_date) the_year
            ,EXTRACT (MONTH FROM the_date) the_month
            ,the_date
        FROM 
            (
                SELECT distinct id
                FROM
                    (
                        SELECT 9245 id, 137 value, DATE '2021-01-18' datetime 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
                    ) table_name
            ) PS CROSS JOIN
            ( --  in this sub query you can change the **start date** and **end date** to change the ranges
                SELECT     
                    MIN(TO_DATE('2021-01-01' /*start date*/, 'YYYY-MM-DD') + LEVEL - 1) the_date
                FROM DUAL
                CONNECT BY 
                    TO_DATE('2021-01-01' /*start date*/, 'YYYY-MM-DD') + LEVEL - 1 <= TO_DATE('2021-10-01' /*end date*/, 'YYYY-MM-DD')
                GROUP BY EXTRACT (YEAR FROM TO_DATE('2021-01-01' /*start date*/, 'YYYY-MM-DD') + LEVEL - 1)
                    ,EXTRACT (MONTH FROM TO_DATE('2021-01-01' /*start date*/, 'YYYY-MM-DD') + LEVEL - 1)
            ) the_dates
        ) FE LEFT OUTER JOIN --end from1
        (
            SELECT
                table_name.id id
                , EXTRACT(MONTH FROM table_name.datetime) the_month
                , EXTRACT(YEAR FROM table_name.datetime)  the_year
                ,MAX(table_name.datetime) datetime
                ,SUM(table_name.value) value
            FROM
                (
                    SELECT 9245 id, 137 value, DATE '2021-01-18' datetime 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
                ) table_name
            GROUP BY table_name.id, EXTRACT(YEAR FROM table_name.datetime), EXTRACT(MONTH FROM table_name.datetime)
        ) Con ON FE.id = Con.id AND FE.the_year = CON.the_year AND FE.the_month = CON.the_month
    GROUP BY FE.id

Note: this query also recognizes the missing dates automatically

Upvotes: 0

MT0
MT0

Reputation: 167972

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 1

Related Questions