ORACLE - How to use LAG to display strings from all previous rows into current row

Question

I have data like below:

group	seq	activity
A	1	scan
A	2	visit
A	3	pay
B	1	drink
B	2	rest

I expect to have 1 new column "hist" like below:

group	seq	activity	hist
A	1	scan	NULL
A	2	visit	scan
A	3	pay	scan, visit
B	1	drink	NULL
B	2	rest	drink

I was trying to solve with LAG function, but LAG only returns one row from previous instead of multiple. Truly appreciate any help!

Answer 1

To aggregate strings in Oracle we use LISAGG function.

In general, you need a windowing_clause to specify a sliding window for analytic function to calculate running total.

But unfortunately LISTAGG doesn't support it.

To simulate this behaviour you may use model_clause of the select statement. Below is an example with explanation.

select
  group_
  , activity
  , seq
  , hist
from t
model
  /*Where to restart calculation*/
  partition by (group_)
  /*Add consecutive numbers to reference "previous" row per group.
    May use "seq" column if its values are consecutive*/
  dimension by (
    row_number() over(
      partition by group_
      order by seq asc
    ) as rn
  )
  measures (
    /*Other columnns to return*/
    activity
    , cast(null as varchar2(1000)) as hist
    , seq
  )
  rules update (
    /*Apply this rule sequentially*/
    hist[any] order by rn asc =
      /*Previous concatenated result*/
      hist[cv()-1]
      /*Plus comma for the third row and tne next rows*/
      || presentv(activity[cv()-2], ',', '') /**/
      /*lus previous row's value*/
      || activity[cv()-1]
  )

GROUP_ | ACTIVITY | SEQ | HIST      
:----- | :------- | --: | :---------
A      | scan     |   1 | null      
A      | visit    |   2 | scan      
A      | pay      |   3 | scan,visit
B      | drink    |   1 | null      
B      | rest     |   2 | drink     

db<>fiddle here

Answer 2

Few more variants (without subqueries):

SELECT--+ NO_XML_QUERY_REWRITE
       t.*,
       regexp_substr(
         listagg(activity, ',')
            within group(order by SEQ)
            over(partition by "GROUP")
        ,'^([^,]+,){'||(row_number()over(partition by "GROUP" order by seq)-1)||'}'
        )
        AS hist1
      ,xmlcast(
         xmlquery(
           'string-join($X/A/B[position()<$Y]/text(),",")'
           passing
            xmlelement("A", xmlagg(xmlelement("B", activity)) over(partition by "GROUP")) as x
           ,row_number()over(partition by "GROUP" order by seq) as y
          returning content
          )
         as varchar2(1000)
         ) hist2
FROM   table_name t;

DBFIddle: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=9b477a2089d3beac62579d2b7103377a

Full test case with output:

with table_name ("GROUP", seq, activity) AS (
SELECT 'A', 1, 'scan'  FROM DUAL UNION ALL
SELECT 'A', 2, 'visit' FROM DUAL UNION ALL
SELECT 'A', 3, 'pay'   FROM DUAL UNION ALL
SELECT 'B', 1, 'drink' FROM DUAL UNION ALL
SELECT 'B', 2, 'rest'  FROM DUAL
)
SELECT--+ NO_XML_QUERY_REWRITE
       t.*,
       regexp_substr(
         listagg(activity, ',')
            within group(order by SEQ)
            over(partition by "GROUP")
        ,'^([^,]+,){'||(row_number()over(partition by "GROUP" order by seq)-1)||'}'
        )
        AS hist1
      ,xmlcast(
         xmlquery(
           'string-join($X/A/B[position()<$Y]/text(),",")'
           passing
            xmlelement("A", xmlagg(xmlelement("B", activity)) over(partition by "GROUP")) as x
           ,row_number()over(partition by "GROUP" order by seq) as y
          returning content
          )
         as varchar2(1000)
         ) hist2
FROM   table_name t;

GROUP         SEQ ACTIV HIST1                          HIST2
------ ---------- ----- ------------------------------ ------------------------------
A               1 scan
A               2 visit scan,                          scan
A               3 pay   scan,visit,                    scan,visit
B               1 drink
B               2 rest  drink,                         drink