Oracle SQL - Returning the count from a delimited field

Question

I'm fairly inexperienced with SQL so hopefully this question is not too silly. Here is the scenario:

I have a VARCHAR2 column that stores a series of values delimited by product. Depending on on the account, they can have one or multiple products. I'm trying to write a query that will return the values but also provide a count for each type or product.

For example:

ProductColumn: P1, P2, P3, P4
Table: TableAccount
Sample Value 1: P1:P2:P3
Sample Value 2: P1
Sample Value 3: P2:P3

My current query only returns a count of all different value types including the delimited values:

select 
ProductColumn,
count(8) cnt
from TableAccount
group by ProductColumn

Any suggestions would be appreciated!

Sample data would help to clarify what you want... ProductColumn has delimited values instead of just one value and you want to explode all of them out? — TheMadDBA, Jun 22 '15 at 20:43
You could try something like this and then do the group count: http://stackoverflow.com/questions/26407538/split-string-into-rows-oracle-sql — Steve Greene, Jun 22 '15 at 20:46
There's not enough info here to give a good answer. It looks like you're going to need to find a good [splitting](http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) function to split your comma-separated values into rows. — DiyDave, Jun 22 '15 at 20:46
Thanks all. What I am trying to do is return a count of all products but not by delimited value. So for example, instead of the value showing up as: P1:P2:P3, I would want the value to show up as P1 - 2, P2 - 2, P3 - 2. — Jubair Ahmed, Jun 22 '15 at 21:16

score 2 · Answer 1 · answered Jun 22 '15 at 20:47

If the product codes are reliable separated by colons, you can use substring to pull the code values, separate from the separator colons. That allows you to return then to the caller, each in separate fields, so summing, grouping, etc. However, that will get messy if any of the values are longer than two bytes. This is why data normalization rules specifically spell out not putting more than one piece of data into a single table column. If it were me, I'd write a PL SQL that splits them out and writes it all cleanly to a NORMALIZED table, then queries from that table. And I would be all over my boss about getting this design flaw FIXED.

score 0 · Accepted Answer · answered Jun 22 '15 at 21:52

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TableAccount ( value ) AS
          SELECT 'P1:P2:P3' FROM DUAL
UNION ALL SELECT 'P1' FROM DUAL
UNION ALL SELECT 'P2:P3' FROM DUAL
UNION ALL SELECT 'P1:P3' FROM DUAL
UNION ALL SELECT 'P1:P4' FROM DUAL
UNION ALL SELECT 'P5' FROM DUAL;

Query 1:

SELECT item,
       COUNT(1) AS frequency
FROM   (
        SELECT REGEXP_SUBSTR( value, '[^:]+', 1, COLUMN_VALUE ) AS item
        FROM   TableAccount t,
               TABLE(
                  CAST(
                    MULTISET(
                      SELECT LEVEL
                      FROM   DUAL
                      CONNECT BY  LEVEL <= REGEXP_COUNT( t.value, '[^:]+')
                    ) AS sys.OdciNumberList
                  )
                )
        )
GROUP BY item
ORDER BY item

Results:

| ITEM | FREQUENCY |
|------|-----------|
|   P1 |         4 |
|   P2 |         2 |
|   P3 |         3 |
|   P4 |         1 |
|   P5 |         1 |

Oracle SQL - Returning the count from a delimited field

2 Answers2

Linked