Oracle, типичные задачи SQL. Трансформация перечисленных в колонке значений в строки таблицы

[alexeyknyshev]

Это всё конечно интересно. Но такие задачи возникают при наличии ненормализованных отношений в таблицах, а именно условие 1NF в данном случае нарушено. Фактически, то, что вы проделали: нормализовали данные для отношений <Актриса, Муж>. А следовательно главная проблема тут кроется именно не в том, как это сделать средставми SQL, а в том, что изначально архитектура отношений построена неверно (и решать надо именно эту проблему в первую очередь).

[Alhymik]

Тут вообще проблемы нет. Я показал, как решать типичную задачу SQL, которая встречается сплошь и рядом, безотносительно к архитектуре отношений. На практике не у всех, кто выбирает данные, есть доступ к базе на запись, а уж тем более изменение структуры. Работаем, как правило, с тем, что есть. А последний раз на практике я с подобным сталкивался, работая с настроечной таблицей отчетов, где кроме уникального ID отчета, были поля с настройками, клобы со скриптами, и одно из полей — это список ролей пользователей через запятую, для которых формирование отчета будет разрешено. Табличка была небольшая, «разматывание» ролей, чтоб сопоставить их с системными, в производительности ничего не отнимало, что-то нормализовывать тупо не было смысла. Т. е. стереотип о том, что ненормализовано, значит неправильно, на практике работает не всегда.

[xtender]

Не совсем верно, например, такие задачи часто возникают, когда хотят получить отчет в разрезах по указанным периодам, и для этого часто нужен генератор дат.

[vbif]

Если всё подряд нормализовывать, то любой самый безобидный запрос будет превращаться в стопку джоинов.

[svetasmirnova]

Блин, я думала спам на Хабре

[Alhymik]

А да, блин. Сместил чуть кат. Спасибо за замечание.

[CertainMan]

Oracle в наличии нет, но есть желание решить задачку и есть PostgreSQL:

select
  hw.actress, h.h as husband, case when h.h is not null then h.n end as husb_no
from hollywood hw,
rows from (
  regexp_split_to_table(husbands, E'\\s*,\\s*'),
  nullif(id, null)
) with ordinality h (h, x, n)
order by hw.id, h.n;

Оно же с комментариями:

select
  hw.actress,
  h.h as husband,
  -- Выводятся номера только для тех строк, которые сформированы с помощью regexp_split_to_table
  case when h.h is not null then h.n end as husb_no
from hollywood hw,
-- ROWS FROM позволяет комбинировать результаты работы нескольких функций
rows from (
  -- Функция разбивает столбец husbands по запятой (попутно удаляя все пробелы по обе стороны запятой),
  -- выводя каждый фрагмент отдельной строкой. При этом для строк с husbands is null ничего не выводится.
  -- E'...' - строка, "заэскейпленная" в стиле C
  regexp_split_to_table(husbands, E'\\s*,\\s*'),
  -- Функция используется, чтобы сохранить в выводе строки с husbands is null (ликвидирует дискриминацию Шарлиз)
  -- Просто возвращает id, если он не null, что гарантирует наличие в выводе всех строк исходной таблицы
  nullif(id, null)
  -- Нумерует строки, получаемые из ROWS FROM
  -- Нумерация начинается с начала (с 1) для каждой строки исходной таблицы
) with ordinality
  -- Задается алиас для результатов конструкции ROWS FROM
  h (h, x, n)
order by hw.id, h.n;

[GetShuk]

Перед использованием rownum рекомендуется делать order by во избежание косангенса в данных при дальнейших манипуляциях. Эт так, заметка на будущее :)