Solved

Query to split one row to multiple rows.

Posted on 2014-09-11
3
341 Views
Last Modified: 2014-09-17
I have records like below.

col1      col2
11-Sep-14       11-Oct-14
15-Sep-14 18-Sep-14

In the first row I am having col1 & col2 in different months, so first record should be split like below

col1                      col2
11-sep-14          30-sep-2014
1-oct-14             11-oct-2014


In the second row, I just have values from same month,
so it should as it is. so altogether it should be 3 rows.
0
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40316909
SELECT GREATEST(col1, ADD_MONTHS(TRUNC(col1, 'mm'), COLUMN_VALUE)) col1,
       LEAST(col2, LAST_DAY(ADD_MONTHS(TRUNC(col1, 'mm'), COLUMN_VALUE))) col2
  FROM yourtable,
       TABLE(
               SELECT COLLECT(LEVEL - 1)
                 FROM DUAL
           CONNECT BY ADD_MONTHS(TRUNC(col1, 'mm'), LEVEL - 1) <= TRUNC(col2, 'mm')
       )
0
 

Author Comment

by:sakthikumar
ID: 40322424
Hi SDSTUBER,

This query works exactly for me, just like to know if I achieve this through outer joins...

just like to have your expert opinion on this.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40322916
There are probably some special cases where you could make an outer join work, but as a general technique for this kind of row expansion, no outer joins aren't really a viable option.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alternative to GTT for a temp table for further reuse in Oracle 8 62
subtr returning incorrect value 8 101
Error in creating a view. 8 52
Loading flat file data in tables 2 101
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question