title: Oracle How to get the count of occurrences within a comma separated string
body: <p>I have a string that is a comma separated value stored in a table.  I want the description and count of each item in the string.  So, if one record has the string 'YL', I want to get 'YL Description', 1.  But if the record has 'YL,YB,YB', I want to get 'YL description', 1 AND 'YB Description', 2.</p>
  
  <p>Using the following fake tables to create data:</p>
  
  <pre><code>create table temp1 (cd_vals varchar2(20 byte));
  insert into temp1 (cd_vals) values ('YB,YL');
  insert into temp1 (cd_vals) values ('YB,YL,YL,YL');
  insert into temp1 (cd_vals) values ('YL');
  
  create table temp2 (cd_val varchar2(2 byte), cd_desc varchar2(20 byte));
  insert into temp2 (cd_val, cd_desc) values ('YB','YB Description');
  insert into temp2 (cd_val, cd_desc) values ('YL','YL Description');
  </code></pre>
  
  <p>I've got a query that returns each value from the table1, the lookup description, and a count of ALL instances in each original string.  (Split might be a function that is internal to our system, but it takes a comma delimited string and returns each entry.  I should be able to use it in the solution, if I can wrap my head around it.)</p>
  
  <p>Using this query:</p>
  
  <pre><code>SELECT t1.cd_vals
       , t2.cd_desc
       , regexp_count(t1.cd_vals, '[^,]+')
  FROM temp1 t1
  join temp2 t2
    on rtrim(t2.cd_val) in (select column_value from table(split(rtrim(t1.cd_vals))))
  order by cd_vals;
  </code></pre>
  
  <p>I get the following results:</p>
  
  <pre><code>cd_vals     cd_desc         count
  YB,YL       YB Description  2
  YB,YL       YL Description  2
  YB,YL,YL,YL YL Description  4
  YB,YL,YL,YL YB Description  4
  YL          YL Description  1
  </code></pre>
  
  <p>But what I really want is:</p>
  
  <pre><code>cd_vals     cd_desc         count
  YB,YL       YB Description  1
  YB,YL       YL Description  1
  YB,YL,YL,YL YL Description  1
  YB,YL,YL,YL YB Description  3
  YL          YL Description  1
  </code></pre>
  
  <p>How do I get that last count field so it shows the count of the specific lookup value in the first string?</p>
  
  <p>Related, but doesn't quite seem to be what I'm looking for are <a href="https://stackoverflow.com/questions/20554511/count-number-of-occurrences-of-keyword-in-comma-separated-column">Count number of occurrences of keyword in comma separated column?</a> and <a href="https://stackoverflow.com/questions/21832577/how-to-get-the-count-of-occurrence-from-comma-separated-string">How to get the count of occurrence from comma separated string</a>.  (Or maybe I'm just not quite looking at them right.)</p>