there is a way to transpose two strings and have a table as result?

Question

I have the next two strings:

String_Cod   = 14521;65412;65845
String_Flags = 1;0;1


for code 14521 the flag is 1
for code 65412 the flag is 0
for code 65845 the flag is 1
in this order always

The result must be something like

I'm start with this query:

select regexp_substr(to_char(:STRING_COD),'[^;]+', 1, level)
from dual
connect BY regexp_substr(to_char(:STRING_COD), '[^;]+', 1, level)
is not null

select regexp_substr(to_char(:STRING_FLAGS),'[^;]+', 1, level)
from dual
connect BY regexp_substr(to_char(:STRING_FLAGS), '[^;]+', 1, level)
is not null

But i don't have an idea how continue to join both and get the result i need.

Can somebody give an advise?

Regards

Alex Poole · Accepted Answer · 2021-04-14T22:26:40.113

You could add the level as another column in each query, and join them together:

select c.cod, f.flag
from (
  select level as n, regexp_substr(to_char('14521;65412;65845'),'[^;]+', 1, level) as cod
  from dual
  connect BY regexp_substr(to_char('14521;65412;65845'), '[^;]+', 1, level)
  is not null
) c
join (
  select level as n, regexp_substr(to_char('1;0;1'),'[^;]+', 1, level) as flag
  from dual
  connect BY regexp_substr(to_char('1;0;1'), '[^;]+', 1, level)
  is not null
) f
on f.n = c.n

which - with outer joins - would allow for different numbers of elements; or more simply as you suggest they will always match, use the same level for both extracts:

select regexp_substr(to_char('14521;65412;65845'),'[^;]+', 1, level) as cod,
  regexp_substr(to_char('1;0;1'),'[^;]+', 1, level) as flag
from dual
connect BY regexp_substr(to_char('14521;65412;65845'), '[^;]+', 1, level)
is not null

COD   | FLAG
:---- | :---
14521 | 1   
65412 | 0   
65845 | 1

db<>fiddle

This method of expanding a list of values also assumes you can never have null elements, in either list. Read more.

there is a way to transpose two strings and have a table as result?

1 Answers1