根据另一列的值，用分号将列值拆分成单独的行

Question

我需要根据quantity值生成行。

条形码列的值将由分号";“分隔。我希望将每个分隔的值转换为单独的列。

如果条码的值小于数量，则条码列的其他行将为空。条形码的值不会大于数量。

| Name         | Quantity    | Barcode
+--------------+-------------+-----------------------------
| Apple        | 5           | barcode1;barcode2;barcode3

预期输出：

| Name         | Barcode
+--------------+----------
| Apple        | barcode1
| Apple        | barcode2
| Apple        | barcode3
| Apple        | 
| Apple        | 

​

​

Answer 1

如果要一次拆分多个源行，则connect-by方法需要一些条件来防止交叉匹配；因此，您可以这样做：

select name, regexp_substr(barcode, '(.*?)(;|$)', 1, level, null, 1)
from your_table
connect by name = prior name
and prior sys_guid() is not null
and level <= quantity;

由于您在11g上，并假设它是11gR2，您还可以使用递归CTE：

with rcte (name, pos, barcode, quantity, all_barcodes) as (
  select name, 1, regexp_substr(barcode, '(.*?)(;|$)', 1, 1, null, 1), quantity, barcode
  from your_table
  union all
  select name, pos + 1, regexp_substr(all_barcodes, '(.*?)(;|$)', 1, pos + 1, null, 1), quantity, all_barcodes
  from rcte
  where pos < quantity
)
select name, barcode
from rcte
order by name, pos;

db<>fiddle

Answer 2

您可以使用CROSS JOIN拆分字符串，即使表中有多行，仍然可以获得正确的行数。

WITH
    fruit (name, quantity, barcode)
    AS
        (SELECT 'Apple', 5, 'barcode1;barcode2;barcode3' FROM DUAL
         UNION ALL
         SELECT 'Banana', 4, 'A;B;C' FROM DUAL)
SELECT f.name, s.COLUMN_VALUE AS barcode
  FROM fruit  f
       CROSS JOIN
       TABLE (CAST (MULTISET (    SELECT REGEXP_SUBSTR (f.barcode,
                                                        '[^;]+',
                                                        1,
                                                        LEVEL)
                                    FROM DUAL
                              CONNECT BY LEVEL <= f.quantity) AS SYS.OdciVarchar2List)) s;

Answer 3

您可以使用以下代码来获得所需的结果-

SELECT name, regexp_substr(barcode,'[^;]+',1,level) as barcode
  FROM T
CONNECT BY LEVEL <= quantity;

这是fiddle。