Oracle, typical SQL tasks. Transformation of the values ​​listed in the column to table rows

Welcome to Hollywood. I present to you today's heroines



First we find out who was the husband of these beauties. And then with the help of uncomplicated special effects, I will show you in what order they married each other. So this article will be especially interesting for young virgins.
')
Create and populate the base table.

create table hollywood as with t (id, actress, husbands) as ( select 1, ' ', '  ,   ,  ' from dual union all select 2, ' ', null from dual union all select 3, ' ', ' ' from dual ) select * from t; alter table hollywood add primary key (id); 

ID	Actress	Husbands
one	Angelina Jolie	Johnny Lee Miller, Billy Bob Thornton, Brad Pitt
2	Charlize Theron
3	Penelope Cruz	Javier Bardem

The table shows that Angelica was married three times. Her husbands are listed in the column through the separator in the order of their marriage to the actress. We agree that the separator is a comma, and the space after it is just garbage.

Liza Theron has never been married at all (civil marriages do not count), and she, apparently, is still waiting for her IT specialist. So you should take note of it and how to hurry - the lady is already, almost, 40.

Well, Penelope Cruz - married only once. What a boredom.

But this is all a prelude, but in fact you need to get the following result

Actress	Husband	Husband's number p / p
Angelina Jolie	Johnny lee miller	one
Angelina Jolie	Billy Bob Thornton	2
Angelina Jolie	Brad Pitt	3
Charlize Theron
Penelope Cruz	Javier Bardem	one

In fact, you need to perform the operation, the inverse grouping and aggregation function listagg.

We will move from simple to complex. To begin with, I propose to consider a similar task - extracting numbers from a single string with a separator into a tabular set.

 with digit_str as ( select '10,20,30,40,50,100' as str from dual ) select regexp_substr(str, '(\d+)(,|$)', 1, rownum, 'c', 1) ok from digit_str connect by level <= regexp_count(str, '\d+(,|$)') 

We get:

Ok
ten
20
thirty
40
50
100

To understand a query, you need to know how regular expression functions work ( help ). The very idea of ​​the query is as follows:

• New lines are generated using connect by level.
• Regexp_count calculates the number of numbers in a line between delimiters - this number determines the upper limit for the row generator.
• Regexp_substr extracts numbers from a string. The number of the pattern entry in the string (4th regular argument) corresponds to the value of the rownum pseudo column — the n / n number of the generated string. Instead of rownum, you could use level, the result would be the same.

There are other ways to solve the problem, for example, like this:

 select to_number(column_value) from xmltable('10,20,30,40,50,100') 

and instead of regulars, we could use combinations of standard string functions, but we’ll dwell on the query with regular expressions.

In the HOLLYWOOD table, we are not dealing with a sequence of numbers, but with the names of noble men. But they can also be calculated using the regexp_count function and retrieved using the regexp_substr function, according to the method described above. Now you need to remember the recipes for pickling bananas and choose one of the ways to generate strings when their future number is known. For examples, I will use the 3rd and 5th method. But when choosing the most optimal generation method, you need to pay attention to the user comment xtender .

By combining approaches, we get the following.

Special effect number 1.

 select actress, trim(regexp_substr(husbands, '(.+?)(,|$)', 1, level, 'c', 1)) as husband, nvl2(husbands, level, null) as husb_no from hollywood connect by prior id = id and prior dbms_random.value is not null and level <= regexp_count(husbands, '.+?(,|$)') order by id, 3 

Everything is OK - girls are happy in marriages. All but Lisa Theron. For people like Lisa, I used nvl2 in the query.

Special effect number 2.

 select h.actress, trim(regexp_substr(h.husbands, '(.+?)(,|$)', 1, x.column_value, 'c', 1)) as husband, nvl2(h.husbands, x.column_value, null) as husb_no from hollywood h, table(cast(multiset(select level from dual connect by level <= regexp_count(h.husbands, '.+?(,|$)')) as sys.odcinumberlist)) x 

It was a decision through the collection.

UPD: One more special Special Effect No. 3 for Oracle 12c was drawn

 select h.actress, trim(regexp_substr(h.husbands, '(.+?)(,|$)', 1, xn, 'c', 1)) as husband, nvl2(h.husbands, xn, null) as husb_no from hollywood h, lateral(select level n from dual connect by level <= regexp_count(h.husbands, '.+?(,|$)')) x 

Obviously, there are quite a few options for solving the problem. The choice of one or another option is a matter of taste and a question of productivity. If I had an Oscar, I would give it without hesitation to the one who offers the most concise in terms of code and the most optimal way in terms of performance.

In the meantime, everything. Until new meetings.