Mapping multiple values into a column

Question

I have the below table.

How can I map multiple tags that are on one row and turn that into the tag description? There are thousands of combinations.

Table: TAG_SEARCH

DATE        ID     TAGS
6/25/2019   101    1251:1306
6/25/2019   102    1251
6/25/2019   103    1251:1306:1274:1446:1452:1586
6/25/2019   104    1251:1306:1586

Table TAG_MAP

TAG_TYPE    TAG_DESC
  1251        Clothing
  1306        Grocery
  1274        Hardware
  1446        Home_Decor
  1452        Electric
  1586        Plumbing

Expected Results:

DATE       ID   TAGS
6/25/2019  101   Clothing:Grocery
6/25/2019  102   Clothing
6/25/2019  103   Clothing:Grocery:Hardware:Home_Decor:Electric:Plumbing
6/25/2019  104   Clothing:Grocery:Plumbing

Answer 1

Let's consider how to do this in a normalized fashion so that the data is searchable and summable and, basically, useful.

Let's assume you're stuck with your first two tables:

CREATE TABLE TAG_SEARCH
  (TAG_DT         DATE,
   TAG_ID         NUMBER,
   TAGS           VARCHAR2(4000));

CREATE TABLE TAG_MAP
  (TAG_TYPE       NUMBER,
   TAG_DESC       VARCHAR2(100));

and you're looking to store your data in an output table:

CREATE TABLE TAGS_OUT
  (TAG_DT         DATE,
   TAG_ID         NUMBER,
   TAG_DESC       VARCHAR2(100));

Now, if we populate our tables as

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);

and

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);

we can abuse a hierarchical query a bit to get it to spit out the individual tags:

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

and once we're there changing the TAG_TYPE to TAG_DESC is a simple matter of joining in the TAG_MAP table appropriately:

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

dbfiddle here

Answer 2

Agree that the design is anti-relational. This is just to show that SQL can do it. Warning: LISTAGG requires database version 11.2 or later.

UPDATE: I forgot that the order of tag descriptions should match the order of the tag types in the source table. This solution does not respect that order. The regexp_substr answer does.

create table tAG_SEARCH(DTE,ID,TAGS) as
select to_date('6/25/2019','mm/dd/yyyy'),   101,    '1251:1306' from dual union all
select to_date('6/25/2019','mm/dd/yyyy'),   102,    '1251' from dual union all
select to_date('6/25/2019','mm/dd/yyyy'),   103,    '1251:1306:1274:1446:1452:1586' from dual union all
select to_date('6/25/2019','mm/dd/yyyy'),   104,    '1251:1306:1586' from dual;

create Table 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;

select dte, id,
  listagg(tag_desc, ':') within group(order by tag_type) tags
from tag_search ts 
join tag_map tm on instr(tags, tag_type) > 0
group by dte, id
order by dte, id;

DTE         ID   TAGS                                                     
2019-06-25  101  Clothing:Grocery                                          
2019-06-25  102  Clothing                                                  
2019-06-25  103  Clothing:Hardware:Grocery:Home_Decor:Electric:Plumbing    
2019-06-25  104  Clothing:Grocery:Plumbing  

Answer 3

You can use regexp_substr() window analytic function to split for the first step, and then listagg() function to concatenate the explanations in the look-up table(tag_map)

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

Demo