Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

BANNER
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

with tag_search( "Date", id, tags ) as
    (
     select date'2019-06-25',101,'1251:1306'                     from dual union all
     select date'2019-06-25',102,'1251'                          from dual union all
     select date'2019-06-25',103,'1251:1306:1274:1446:1452:1586' from dual union all
     select date'2019-06-25',104,'1251:1306:1586'                from dual
    ), tag_map( tag_type, tag_desc) as
    (
     select 1251, 'Clothing'   from dual union all
     select 1306, 'Grocery'    from dual union all
     select 1274, 'Hardware'   from dual union all
     select 1446, 'Home_Decor' from dual union all
     select 1452, 'Electric'   from dual union all
     select 1586, 'Plumbing'   from dual
    ), t as
    (
    select "Date", id, regexp_substr(tags,'[^:]+',1,level) as tags,
           row_number() over (order by id, level) as lvl
      from tag_search
     connect by level <= regexp_count(tags,':') + 1
        and prior id = id
        and prior sys_guid() is not null
    )
    select "Date", id, listagg(tag_desc,':') within group (order by id, lvl) 
           as tags
      from t
      join tag_map on tag_type = tags
     group by "Date", id;

Date	ID	TAGS
25-JUN-19	101	Clothing:Grocery
25-JUN-19	102	Clothing
25-JUN-19	103	Clothing:Grocery:Hardware:Home_Decor:Electric:Plumbing
25-JUN-19	104	Clothing:Grocery:Plumbing