CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Lee Wen Ping
Lee Wen Ping

Reputation: 13

Split column value by semicolon into individual rows based on another column's value

I need to generate rows based on the quantity value.

The barcode column will have value separate by semicolon ";" I want to turn each separated value into individual column.

If the barcode value is less than the quantity, other rows of barcode column will be null. The barcode value will not be more than the quantity.

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

Expected Output:

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

Example data

Upvotes: 1

Views: 396

Answers (3)

Alex Poole
Alex Poole

Reputation: 191265

If you have more than one source row to split at once, a connect-by approach needs conditions to prevent cross matches; so you can do:

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;

As you are on 11g, and assuming that is 11gR2, You could also use a recursive 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

Upvotes: 2

EJ Egyed
EJ Egyed

Reputation: 6084

You can use a CROSS JOIN to split the strings and still get the correct number of rows even with multiple rows in the table.

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;

Upvotes: 3

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can use below code to get the desired result -

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

Here is the fiddle.

Upvotes: 1

Related Questions