Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Query to split one row to multiple rows.

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
sakthikumar
Asked:
sakthikumar
  • 2
2 Solutions
 
sdstuberCommented:
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
 
sakthikumarAuthor Commented:
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
 
sdstuberCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now