SQL Oracle: замена в строке в соотвт. с данными из другой таблицы

Question

Имею таблицу с колонкой(ми) следующего формата:

list1              | list2
15154,15126        | 15454,10926
10640,10589,11057  | NULL
NULL               | 10989
10497              | 12343

и таблицу

ID    |  item
15154 |item 1
15126 |item 2
10640 |item 3
10497 |item 4
10926 |item 5

Просьба, подсказать, можно ли как-то оптимально в ячейках колонок list1 и list2 заменить данные в соответствии с id из второй таблицы, т.е. получить таблицу на выходе следующего вида:

res_list1      | res_list2
item 1, item 2 | item 1, item 5

Answer 1

Можно рекурсивным запросом разобрать list1 (list2) на части, а потом собрать обратно соответствующие "item N". Со string_split все проще, но его в Oracle нет, насколько я знаю. Все пишут свои процедуры-аналоги string_split.

rq as(
  select tn,pn,ln,1 wn
  ,case when charindex(',',ls)>0 then substring(ls,1,charindex(',',ls)-1)
        else ls 
        end el -- элемент списка
  ,case when charindex(',',ls)>0 
        then substring(ls,charindex(',',ls)+1,len(ls)-charindex(',',ls))
        else ls end rest -- остаток списка
      , ls as orig --оригинальный список, если нужно
    from sl -- таблица со списком элементов
  union all
  select tn,pn,ln,wn+1 wn
  ,case when charindex(',',rest)>0 then substring(rest,1,charindex(',',rest)-1)
        else rest 
        end el
  ,case when charindex(',',rest)>0 
        then substring(rest,charindex(',',rest)+1,len(rest)-charindex(',',rest))
        else rest end rest, orig
      from rq where  rest<> el 
  )

Пример для SQL Server здесь
Насчет оптимальности не знаю, это предложение на уровне "как-нибудь"

Answer 2

Разбить списки на ID, поменять их и снова соединить

create table objects as
        select 1 as obj_id, '15154,15126' as list1, '15154,10926' as list2 from dual union all
        select 2, '10640,10589,11057', '' from dual;
create table items as
        select 15154 as id, 'item 1' as item from dual union all
        select 15126, 'item 2' from dual union all
        select 10640, 'item 3' from dual union all
        select 10497, 'item 4' from dual union all
        select 10926, 'item 5' from dual;

Запрос

with lists as (
    select * from objects
    unpivot(list for column_name in (list1, list2))
)
select
    obj_id, res_list1, res_list2
from
    (
        select
            obj_id, column_name, n, item
        from
            lists
            join (
                select level n
                from dual
                connect by level <= (select max(regexp_count(list, '\d+')) from lists)
            ) on n <= regexp_count(list, '\d+')
            left join items on id = to_number(regexp_substr(list, '\d+', 1, n))
    ) pivot (
        listagg(nvl(item, '?'), ',') within group (order by n)
        for column_name in ('LIST1' as res_list1, 'LIST2' as res_list2)
    )

БЛОГ НА HUSL

SQL Oracle: замена в строке в соотвт. с данными из другой таблицы

Ответы (2 шт):