I would like to generate view in oracle that converts comma-separated entry to columns

Question

I would like to generate view

CUSTOMER, CUSTOMER_ID, PRODUCTS
ABC INC   1            A=XYX, B=ZZZ
DEF CO    2            A=XYX, B=ZZZ, C=WWW
GHI LLC   3            B=ZYX

Would like the view to be something like

CUSTOMER, CUSTOMER_ID, A    B    C
ABC INC   1            XYX  ZZZ
DEF CO    2            XYX  ZZZ  WWW
GHI LLC   3                 ZYX

I was wondering if there is way to do this in oracle is fast and efficient way. I know it can might be done with PLSQL or with some logic. Concern here is mainly performance as I need to pull data every 10 minutes from tables that have enormous amounts of data and don't want the view query to take more than that.

Any ideas or suggestions?

Thanks,

Tam

Answer 1

is there always just A, B and C ? if so, use substr and instr

Answer 2

As Matthew says it's really just a simple matter of string functions to isolate those values, but if you expect to be able to query the view with predicates such as B=XYZ then you're going to have to look into adding function-based indexes on the underlying tables.