How to produce a running sequence in Oracle based on report column values

Question

I have the following sample Oracle APEX report by where the Origin ID and Origin Name are retrieved from the origin_tab(id,origin_id,origin_name)

Based on these two column values, I need to generate the Temporary Origin ID value on the fly as part of the selection retrieval from the origin_tab.

The rule behind this is - where the Origin Name is the same against the Origin ID, take the Origin ID value before the dash and append a 3-digit sequence for each Orgin ID as per example below.

Since the origin name (AAA) is the same for the first two records, '001' is appended to the origin id 1111. The same for BBB, '002' is appended to those three records and so forth.

Please note that the Temporary Origin ID here is a string.

Origin ID         Origin Name        Temporary Origin ID
----------------- ------------------ --------------------
1111-1            AAA                1111001
1111-2            AAA                1111001
1111-3            BBB                1111002
1111-4            BBB                1111002
1111-5            BBB                1111002
1111-6            CCC                1111003
1111-7            DDD                1111004
1111-8            DDD                1111004

What approach should I take in my query to achieve the above result, for the Temporary Origin ID?

Answer 1

Use dense_rank():

select t.*,
       (substr(origin_id, 1, 4) ||
        lpad(dense_rank() over (order by origin_name), 4, '0')
       ) as temp_origin_id
from t;

Here is a db<>fiddle.

Answer 2

Make a flag column and put there 1 every time when name changes. You can detect this using lag() or subquery. Then concatenate analytical sum of flag column to 1111.

select Origin_ID, Origin_Name, 
       '1111'||lpad(sum(flag) over (order by origin_id), 3, '0') Temp_Origin_ID
  from (
    select t.*, 
           case lag(origin_name) over (order by origin_id) when origin_name then 0 else 1 end flag 
      from t)
  order by Origin_ID

^{dbfiddle demo}