How to break a VARCHAR2(4000) string line in Multiple Rows Counting the Words Repetition

Question

I have an Issues DataBase, with many solutions open/closed for each issue in a view. My chalenge is take all the answers, break it in each words it as was writed, and count these words separeted by column for the entire number of issues stored in this Data Base (view).

Sample.:

SELECT * FROM VW_ISSUE_REPORT;

issueID	ProblemReported	Solution	IsClosed
1	Printer Offline	Turn On the Printer ABC	Yes
2	Printer Paper Jam	Remove Paper Jam from Printer ABC	No

Result expected: (This is a historical database, I can't create functions, procedures, etc. Just a smart, and well known SELECT statement.).

SELECT MAGIC_SOLUTION( Solution) AS SolutionKeyWord  ,  COUNT('X') AS SolutionRepetitions FROM VW_ISSUE_REPORT GROUP BY MAGIC_SOLUTION( Solution);

SolutionKeyWord	SolutionRepetitions
ABC	2
Printer	2
from	1
Jam	1
On	1
Paper	1
Remove	1
the	1
Turn	1

Best Regards

Answer 1

As you can't (at least, I think you can't) distinguish "names" from "solutions" (i.e. how to remove "ABC" from result? It can be anything, from HP to Canon to its IP address), one option is to split the whole solution into rows and count how many times each of its words repeats.

Sample data:

SQL> with test (issue_id, problemreported, solution, isclosed) as
  2    (select 1, 'Printer Offline', 'Turn On the Printer ABC', 'Yes' from dual union all
  3     select 2, 'Printer Paper Jam', 'Remove Paper Jam from Printer ABC', 'No' from dual
  4    )

Query begins here:

  5  select regexp_substr(solution, '[^ ]+', 1, column_value) solution_keyword,
  6         count(*) solution_repetitions
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(solution, ' ') + 1
 10                       ) as sys.odcinumberlist))
 11  group by regexp_substr(solution, '[^ ]+', 1, column_value);

SOLUTION_KEYWORD                  SOLUTION_REPETITIONS
--------------------------------- --------------------
Paper                                                1
Printer                                              2
Turn                                                 1
the                                                  1
Remove                                               1
Jam                                                  1
On                                                   1
from                                                 1
ABC                                                  2

9 rows selected.

SQL>