CODEWITHSUNDEEP
sqlstringoracle-databaseoracle11g
thursdaysgeek
thursdaysgeek

Reputation: 7946

Oracle How to get the count of occurrences within a comma separated string

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.

Using the following fake tables to create data:

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

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

Using this query:

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;

I get the following results:

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

But what I really want is:

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

How do I get that last count field so it shows the count of the specific lookup value in the first string?

Related, but doesn't quite seem to be what I'm looking for are Count number of occurrences of keyword in comma separated column? and How to get the count of occurrence from comma separated string. (Or maybe I'm just not quite looking at them right.)

Upvotes: 1

Views: 1702

Answers (1)

Tyler
Tyler

Reputation: 185

Is this what you want?

SELECT cd_vals, cd_val, COUNT(1)
FROM   (SELECT cd_vals,COLUMN_VALUE cd_val
        FROM   temp1 t1, table(split(rtrim(t1.cd_vals))))
JOIN   temp2
USING  (cd_val)
GROUP  BY cd_vals, cd_val

CD_VALS     CD_VAL COUNT(1)
----------- ------ --------
YB,YL       YB            1
YB,YL,YL,YL YL            3
YL          YL            1
YB,YL       YL            1
YB,YL,YL,YL YB            1

5 rows returned.

Upvotes: 4

Related Questions