I've been working through a task of trying to classify several million rows of data into a variety of different topics. The data involves calls from our customer support, and we're trying to find a way to classify each call into one of 109 topics. Due to the confidentiality of the data I can't disclose any of the actual data, but will try to give a relatable subset of data that other people could compare to.

DATA:

Incident_Number | Call_Description
000123456 | Issue with oranges and apples
000987654 | oranges
004567891 | with apples and kiwis
026589741 | Issue with kiwis

select
Incident_Number,
Call_Description,
(case when call_description like '%oranges%' then oranges
when call_description like '%apples%' then apples
when call_descritpion like '%kiwis%' then 'kiwis'
else 'Unclassified' end) Topic
from DATA

Question

My hope would be to have Incident 000123456 classified as both oranges and apples and Incident 004567891 get classified as apples and kiwis

Desired Output

Incident_Number | Call_Description ......................| Topic
000123456 ........ | Issue with oranges and apples | oranges
000123456 ........ | Issue with oranges and apples | apples
000987654 ........ | oranges ...................................| oranges
004567891 .........| with apples and kiwis............... | apples
004567891 .........| with apples and kiwis............... | kiwis
026589741 .........| Issue with kiwis........................ | kiwis

Wrapup

From my limited knowledge and what I've garnered from research a simple case statement can't do this because it short circuits after finding the first true value. My question is whether or not it is possible to make some alterations to my code OR instead to somehow set up a cursor to run through my initial table and give me the desired output noted above.

I appreciate any help or advice and hope that I've adhered to the rules of this website (which has honestly saved my butt before!)

Regards, Richard

I use Microsoft SQL Server instead of Oracle, so I'm not sure about the Oracle syntax, but one solution I have used in the past is to create a temporary table:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  groupName  varchar(50)
) ON COMMIT DELETE ROWS;
Insert Into my_temp_table (groupName) VALUES('oranges')
Insert Into my_temp_table (groupName) VALUES('apples')
Insert Into my_temp_table (groupName) VALUES('kiwis')

then I would inner join to the table to duplicate the records:

select
    Incident_Number,
    Call_Description,
    my_temp_table.groupName Topic
from DATA
inner join my_temp_table
    on Data.call_description like '%' + my_temp_table.groupName + '%'

One problem with this method is that if a record doesn't fall into any categories, it will be excluded completely.