I have two columns in an Oracle 19c db



I want to make a select query which will return one column like this:


I`m not able to use any for loop. Values from C1 are always equal with C2, delimiter is always ',' and can be N values in this columns.

Thank you for you time.


select 'TOTAL EUR, USD, EUR' as c1,'300,120,200' as c2 from dual;
C1                  | C2         
:------------------ | :----------
TOTAL EUR, USD, EUR | 300,120,200
select 'TOTAL EUR 300'||chr(10)||'USD 120'||chr(10)||'EUR 200' as c12 from dual;
| C12                                 |
| :---------------------------------- |
  USD 120
  EUR 200 

db<>fiddle here

  • Please provide your table structures and some sample data with your desired result on dbfiddle.uk - post that same info here!
    – Vérace
    Jun 9 at 14:38

I have two columns in an Oracle 19c db ... 'a,b,c,d'

I don't think you should be looking for a query to do this work at all, unless you're trying to perform a one-off Data Normalisation exercise to get rid of this fundamental mistake in table design.

More than one value in any column is [almost always] just plain wrong and should be split out into multiple rows instead. Trying to do anything with that delimited field will be a complete nightmare for you, either in its coding, or in the performance for the resulting queries, or both.

This data probably ought to look something more like this:

| C1 | C2 |
| a  |  1 |
| b  |  2 |
| c  |  3 |
| d  |  4 |
| x  |  1 |
| y  |  2 |
| z  |  3 |
  • Yes, should, but not. The data looks like I showed you above. My task is to make a view to looks like that.
    – Luc
    Jun 9 at 15:29
  • The Task you have been given will only perpetuate a basic mistake that needs to be addressed before any more Technical Debt is poured away on top of it. If this data is used a lot in this application, then it will eventually kill performance. Go back to whoever stores the data this way and explain to them that they are doing it wrongly. For reference, point them to just about any Database Design source - this is /that/ fundamental. Simple data structures work well in all cases. What you have here possibly works well in one case but will be terrible in every other.
    – Phill W.
    Jun 10 at 12:58

I found a solution for what I want but I think is not the best.

|   COLUMN1      | COLUMN2          |
| ---------------| ---------------- |
| TOTAL, EUR, USD| 300,200,100      |

select listagg(x.c1||': '||x.c2, chr(10))
from (
select distinct
REGEXP_SUBSTR (column1, '[^,]+', 1, level)   as c1,
REGEXP_SUBSTR (column2, '[^,]+', 1, level)  as c2
from test
connect by regexp_substr(column1,'[^,]+',1,level) is not null
connect by regexp_substr(column2,'[^,]+',1,level) is not null


TOTAL: 300
 EUR: 200
 USD: 100

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.