Skip to Main Content
  • Questions
  • Delimited column to split into multiple rows

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sushil.

Asked: January 23, 2017 - 10:39 am UTC

Last updated: September 07, 2018 - 3:04 pm UTC

Version: Oracle Database 10g R2

Viewed 10K+ times! This question is

You Asked

Please refer to the LiveSQl link for table creation and how the data is within the table. Is there a direct way to achieve this using SQL. I am aware of commands to split for each record so that Cartesian output is created but not sure if something like below is possible to do. Thanks!

Current Table
===========
ID Name Comments
1 ABC Test1; Test2:Test3
1 XYZ Test1; Test2:Test3

Required Output
=============
ID Name Comments
1 ABC Test1
1 XYZ Test2
1 Test3

with LiveSQL Test Case:

and Chris said...

So you want to:

- Convert the comments to rows
- For each of these generated rows, link to one of the existing table rows
- If there's more rows from the comments than in the table, fill down the IDs

?

If so, you can do something like:

- Split the comments to rows; assign a row number to each distinct value using dense_rank()
- Assign a row_number to each row in the table
- Partition outer join by ID the table to the generated rows. This will fill down the IDs for you:

Create table testdrop(ID Number,Name Varchar2(10), Comments Varchar2(100));

insert into testdrop values (1,'ABC','Test1; Test2:Test3');
insert into testdrop values (1,'XYZ','Test1; Test2:Test3');

with rws as (
select 
  distinct
  trim(regexp_substr(t.comments, '[^;:]+', 1, levels.column_value))  as error, 
  dense_rank() over (order by trim(regexp_substr(t.comments, '[^;:]+', 1, levels.column_value))) rn
from 
  testdrop t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.comments, '[^;:]+'))  + 1) as sys.OdciNumberList)) levels
), t as (
  select t.*, row_number() over (order by id, name) rn from testdrop t
)
  select id, name, error from rws
  left join t partition by (id)
  on     rws.rn = t.rn
  order  by rws.rn;

ID  NAME  ERROR  
1   ABC   Test1  
1   XYZ   Test2  
1         Test3


For more on partitioned outer joins, read this by Adrian Billington:

http://www.oracle-developer.net/display.php?id=312

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sushil Shetty, January 23, 2017 - 6:13 pm UTC

Thanks Chris!
This does not work when there are more rows in Name fields as compared to delimited values in comments field. Also it add the rows for existing values.

Edited the LiveSQL code https://livesql.oracle.com/apex/livesql/s/eg23eidf80om9qkt26rv815xq

Chris Saxon
January 24, 2017 - 2:03 pm UTC

To handle rowsets being different sizes, full outer join the two together on the calculated row numbers and ID.

Make sure you partition by id in row_number() and dense_rank()

Sushil, January 24, 2017 - 11:19 pm UTC

Thanks, that worked!
Connor McDonald
January 25, 2017 - 1:35 am UTC

glad we could help

Ankush, September 07, 2018 - 11:52 am UTC

Why not use Instr and Sustr for the column from a subquery and separate the columns? Cast it as per the data of the target OLAP or OLTP.
Chris Saxon
September 07, 2018 - 3:04 pm UTC

You mean instead of the dual connect by level trick?

I was assuming there could be a variable number of comments contained within each row. Instr/substr fixes you to an upper limit.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.