Oracle 11g Release 2

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

CREATE TABLE TAG_SEARCH
  (TAG_DT         DATE,
   TAG_ID         NUMBER,
   TAGS           VARCHAR2(4000));
CREATE TABLE TAG_MAP
  (TAG_TYPE       NUMBER,
   TAG_DESC       VARCHAR2(100));
CREATE TABLE TAGS_OUT
  (TAG_DT         DATE,
   TAG_ID         NUMBER,
   TAG_DESC       VARCHAR2(100));
MERGE INTO TAG_SEARCH ts
  USING (SELECT TO_DATE('6/25/2019', 'MM/DD/YYYY') AS TAG_DT, 101 AS TAG_ID, '1251:1306' AS TAGS FROM DUAL UNION ALL
         SELECT TO_DATE('6/25/2019', 'MM/DD/YYYY') AS TAG_DT, 102 AS TAG_ID, '1251' AS TAGS FROM DUAL UNION ALL
         SELECT TO_DATE('6/25/2019', 'MM/DD/YYYY') AS TAG_DT, 103 AS TAG_ID, '1251:1306:1274:1446:1452:1586' AS TAGS FROM DUAL UNION ALL
         SELECT TO_DATE('6/25/2019', 'MM/DD/YYYY') AS TAG_DT, 104 AS TAG_ID, '1251:1306:1586' AS TAGS FROM DUAL) d
    ON (d.TAG_ID = ts.TAG_ID)
WHEN NOT MATCHED THEN
  INSERT (TAG_DT, TAG_ID, TAGS) VALUES (d.TAG_DT, d.TAG_ID, d.TAGS);
4 rows affected

MERGE INTO TAG_MAP tm
  USING (SELECT 1251 AS TAG_TYPE, 'Clothing' AS TAG_DESC FROM DUAL UNION ALL
         SELECT 1306 AS TAG_TYPE, 'Grocery' AS TAG_DESC FROM DUAL UNION ALL
         SELECT 1274 AS TAG_TYPE, 'Hardware' AS TAG_DESC FROM DUAL UNION ALL
         SELECT 1446 AS TAG_TYPE, 'Home_Decor' AS TAG_DESC FROM DUAL UNION ALL
         SELECT 1452 AS TAG_TYPE, 'Electric' AS TAG_DESC FROM DUAL UNION ALL
         SELECT 1586 AS TAG_TYPE, 'Plumbing' AS TAG_DESC FROM DUAL) d
    ON (d.TAG_TYPE = tm.TAG_TYPE)
WHEN NOT MATCHED THEN
  INSERT (TAG_TYPE, TAG_DESC) VALUES (d.TAG_TYPE, d.TAG_DESC);
6 rows affected

SELECT DISTINCT TAG_ID, LEVEL, TAG_DT, REGEXP_SUBSTR(TAGS,'[^:]+', 1, LEVEL) AS TAG_TYPE
  FROM TAG_SEARCH
  CONNECT BY REGEXP_SUBSTR(TAGS,'[^:]+', 1, LEVEL) IS NOT NULL
  ORDER BY TAG_ID, LEVEL


TAG_ID
LEVEL
TAG_DT
TAG_TYPE


101
1
25-JUN-19
1251

101
2
25-JUN-19
1306

102
1
25-JUN-19
1251

103
1
25-JUN-19
1251

103
2
25-JUN-19
1306

103
3
25-JUN-19
1274

103
4
25-JUN-19
1446

103
5
25-JUN-19
1452

103
6
25-JUN-19
1586

104
1
25-JUN-19
1251

104
2
25-JUN-19
1306

104
3
25-JUN-19
1586


SELECT q.TAG_ID, q.LVL, q.TAG_DT, tm.TAG_DESC
  FROM (SELECT DISTINCT TAG_ID, LEVEL AS LVL, TAG_DT, REGEXP_SUBSTR(TAGS,'[^:]+', 1, LEVEL) AS TAG_TYPE
          FROM TAG_SEARCH
          CONNECT BY REGEXP_SUBSTR(TAGS,'[^:]+', 1, LEVEL) IS NOT NULL
          ORDER BY TAG_ID, LEVEL) q
  INNER JOIN TAG_MAP tm
    ON tm.TAG_TYPE = q.TAG_TYPE
  ORDER BY q.TAG_ID, q.LVL


TAG_ID
LVL
TAG_DT
TAG_DESC


101
1
25-JUN-19
Clothing

101
2
25-JUN-19
Grocery

102
1
25-JUN-19
Clothing

103
1
25-JUN-19
Clothing

103
2
25-JUN-19
Grocery

103
3
25-JUN-19
Hardware

103
4
25-JUN-19
Home_Decor

103
5
25-JUN-19
Electric

103
6
25-JUN-19
Plumbing

104
1
25-JUN-19
Clothing

104
2
25-JUN-19
Grocery

104
3
25-JUN-19
Plumbing

TAG_ID	LEVEL	TAG_DT	TAG_TYPE
101	1	25-JUN-19	1251
101	2	25-JUN-19	1306
102	1	25-JUN-19	1251
103	1	25-JUN-19	1251
103	2	25-JUN-19	1306
103	3	25-JUN-19	1274
103	4	25-JUN-19	1446
103	5	25-JUN-19	1452
103	6	25-JUN-19	1586
104	1	25-JUN-19	1251
104	2	25-JUN-19	1306
104	3	25-JUN-19	1586

TAG_ID	LVL	TAG_DT	TAG_DESC
101	1	25-JUN-19	Clothing
101	2	25-JUN-19	Grocery
102	1	25-JUN-19	Clothing
103	1	25-JUN-19	Clothing
103	2	25-JUN-19	Grocery
103	3	25-JUN-19	Hardware
103	4	25-JUN-19	Home_Decor
103	5	25-JUN-19	Electric
103	6	25-JUN-19	Plumbing
104	1	25-JUN-19	Clothing
104	2	25-JUN-19	Grocery
104	3	25-JUN-19	Plumbing