Início / dba / Perguntas / 324220
Accepted
Pantea
Asked: 2023-03-01 21:02:55 +0800 CST

Como transformar uma string com valores "separados por pipe" em linhas individuais no Oracle PL/SQL

  • 772

Tenho uma tabela com a estrutura abaixo:

create table student_info
(
  item_number    number,
  st_firstname   varchar2(50),
  st_lastname    varchar2(50),
  st_score       varchar2(50)
)

Aqui está um exemplo de dados da tabela:

item_number  |  st_firstname   |         st_lastname      |  st_score
----------------------------------------------------------------------------
     1         Ali|Reza|Pantea    Hashemi|Nosrati|Yaghobi    10|20|20
     
     2          Maryam|Ahmad         Moghise|Majlesi          20|20

Eu preciso ter a saída abaixo:

item_number  |  st_firstname   |         st_lastname      |  st_score
----------------------------------------------------------------------------
     1             Ali                    Hashemi               10
     1             Reza                   Nosrati               20
     1             Pantea                 Yaghobi               20
     
     2             Maryam                 Moghise               20
     2             Ahmad                  Majlesi               20

Descobri que, com a consulta abaixo, posso fazer o que quiser com uma das colunas (que é st_firstname):

select distinct t.item_number,
                trim(regexp_substr(t.st_firstname, '[^|]+', 1, level)) str
  from student_info t
connect by instr(st_firstname, '|', 1, level - 1) > 0
 order by t.item_number

O problema é que não sei como adicionar outras colunas (st_lastname , st_lastname) à consulta acima. Eu queria saber se você poderia me ajudar aqui.

desde já, obrigado

oracle

1 respostas

  1. Best Answer

    Você simplesmente precisa adicionar colunas adicionais, por exemplo:

    WITH student_info AS (SELECT 1 item_number, 'Ali|Reza|Pantea' st_firstname, 'Hashemi|Nosrati|Yaghobi' st_lastname, '10|20|20' st_score FROM dual UNION ALL
                      SELECT 2 item_number, 'Maryam|Ahmad' st_firstname, 'Moghise|Majlesi' st_lastname, '20|20' st_score FROM dual)
SELECT t.item_number,
       LEVEL lvl,
       TRIM(regexp_substr(t.st_firstname, '[^|]+', 1, LEVEL)) first_name,
       TRIM(regexp_substr(t.st_lastname, '[^|]+', 1, LEVEL)) last_name,
       TRIM(regexp_substr(t.st_score, '[^|]+', 1, LEVEL)) score
FROM   student_info t
CONNECT BY PRIOR item_number = item_number
           AND PRIOR sys_guid() IS NOT NULL
           AND instr(st_firstname, '|', 1, LEVEL - 1) > 0
ORDER  BY t.item_number, LEVEL;


ITEM_NUMBER        LVL FIRST_NAME      LAST_NAME               SCORE
----------- ---------- --------------- ----------------------- --------
          1          1 Ali             Hashemi                 10
          1          2 Reza            Nosrati                 20
          1          3 Pantea          Yaghobi                 20
          2          1 Maryam          Moghise                 20
          2          2 Ahmad           Majlesi                 20

    NB, atualizei a connect bycláusula para restringi-la a fazer um loop em cada linha, em vez de tentar conectar hierarquicamente todas as linhas (essa é a loucura! E também desempenho ruim, se o seu conjunto de dados tiver muitas linhas). Observe que, por causa disso, você não precisa mais do distinct, pois as linhas "duplicadas" não são mais produzidas.

    • 1

relate perguntas