SQL - Splitting a single column into multiple rows

Question

I have a record that looks like this in the database (As an example).

ID, Name, Brand
1, 'Bike', 'Schwinn'
2, 'Car', 'Ford, Honda, Chevy'
3, 'Bike', 'Schwinn, Trex'
4, 'Car', 'Honda'

I need to export the data out and create multiple records where Brand has multiple entries. I also need to increase the ID on output so I don't have duplicates. (I can use a sequence for this and would set it higher to my max value in db).

My output would look like

ID, Name, Brand
1, Bike, Schwinn
2, Car, Ford
Sequence.nextval, Car, Honda
Sequence.nextval, Car, Chevy
3, Bike, Schwinn
Sequence.nextval, Bike, Trex
4, Car, Honda

I would like to try and to this with a SQL statement. Basically I'm dumping this data as a csv file via straight SQL.

My difficulty is trying to loop/split through the Brand column.

This sort of problem is a perfect example of why comma-separated fields are a bad idea, and database normalization matters. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Andy Lester, Jun 11 '14 at 02:08

San · Answer 1 · 2014-06-11T01:54:20.187

You can use following select statement:

with test_tab (ID, Name, Brand) as (
select 1, 'Bike', 'Schwinn' from dual union all
select 2, 'Car', 'Ford, Honda, Chevy' from dual union all
select 3, 'Bike', 'Schwinn, Trex' from dual union all
select 4, 'Car', 'Honda' from dual)
--------------------
-- End of Data Preparation
--------------------
select case when level <> 1 then <your_sequece>.nextval else id end as id, 
       name, 
       trim(regexp_substr(Brand, '[^,]+', 1, level)) BRAND
  from test_tab
connect by regexp_substr(Brand, '[^,]+', 1, level) is not null
    and prior Brand = Brand
    and prior sys_guid() is not null;

output would be:

ID      NAME    BRAND
---------------------
2   Car     Ford
5   Car     Honda
6   Car     Chevy
4   Car     Honda
1   Bike    Schwinn
3   Bike    Schwinn
7   Bike    Trex

You can write Insert statement as

Insert into <destination_table>
    select case when level <> 1 then <your_sequece>.nextval else id end as id, 
           name, 
           trim(regexp_substr(Brand, '[^,]+', 1, level)) BRAND
      from <source_table>
    connect by regexp_substr(Brand, '[^,]+', 1, level) is not null
        and prior Brand = Brand
        and prior sys_guid() is not null;

PS: If ID is unique, you can try replacing and prior Brand = Brand with and prior ID = ID to remove duplicate loop values.

select case when level <> 1 then <your_sequece>.nextval else id end as id, 
       name, 
       trim(regexp_substr(Brand, '[^,]+', 1, level)) BRAND
  from <source_table>
connect BY regexp_substr(Brand, '[^,]+', 1, level) is not null
    and prior ID = ID
    and prior sys_guid() is not null;

Thanks San, Great stuff. Unfortunately it doesn't work completely because I have records with duplicate brands such as 1, 'Car', 'Ford, Honda, Chevy', Record 2 = 2, 'Truck', 'Ford, Honda, Chevy' It seems to get caught in some type of loop where I get tons of records — user2092856, Jun 10 '14 at 16:03
Try replacing `prior Brand = Brand` with `and prior ID = ID` coz it seems ID is unique in source data. I have modified the answer. — San, Jun 11 '14 at 01:55

SQL - Splitting a single column into multiple rows

1 Answers1