This discussion is archived
10 Replies Latest reply: Apr 1, 2013 7:05 AM by sunwill RSS

how can I split one row to multiple rows

sunwill Newbie
Currently Being Moderated
Table like this
CREATE TABLE T(ID NUMBER(12),START_DATE DATE,END_DATE DATE,ORDER_ID NUMBER(12),PROD_ID NUMBER(12));
data like this
ID START_DATE END_DATE ORDER_ID PROD_ID
1 2013-01-01 2013-03-31 12 123
2 2013-04-01 2013-06-30 12 123
3 2013-01-01 2013-05-30 12 234
4 2013-02-01 2013-04-30 13 123
5 2013-07-01 2013-09-30 13 345
I want the result like this
ID DIFF_DATE ORDER_ID PROD_ID
1 201301 12 123
2 201302 12 123
3 201303 12 123
4 201304 12 123
5 201305 12 123
6 201306 12 123
7 201301 12 234
8 201302 12 234
9 201303 12 234
10 201304 12 234
11 201305 12 234
12 201302 13 123
13 201303 13 123
14 201304 13 123
15 201307 13 345
16 201308 13 345
17 201309 13 345
how to write the sql ?

Edited by: 990390 on 2013-3-31 下午11:42
  • 1. Re: how can I split one row to multiple rows
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    SQL> WITH t(iD ,START_DATE ,END_DATE ,ORDER_ID ,PROD_ID) AS(
      2  SELECT 1, to_date('2013-01-01','yyyy-mm-dd'), to_date('2013-03-31','yyyy-mm-dd'), 12, 123 FROM dual UNION ALL
      3  SELECT 2, to_date('2013-04-01','yyyy-mm-dd'), to_date('2013-06-30','yyyy-mm-dd'), 12, 123 FROM dual UNION ALL
      4  SELECT 3, to_date('2013-01-01','yyyy-mm-dd'), to_date('2013-05-30','yyyy-mm-dd'), 12, 234 FROM dual UNION ALL
      5  SELECT 4, to_date('2013-02-01','yyyy-mm-dd'), to_date('2013-04-30','yyyy-mm-dd'), 13, 123 FROM dual UNION ALL
      6  SELECT 5, to_date('2013-07-01','yyyy-mm-dd'), to_date('2013-09-30','yyyy-mm-dd'), 13, 345 FROM dual
      7  )
      8  SELECT add_months(MY_DATE , ROW_NUMBER() OVER(PARTITION BY Q.ORDER_ID, Q.PROD_ID ORDER BY ROWNUM)-1) ddate,
      9         Q.ORDER_ID,
     10         Q.PROD_ID
     11    FROM (SELECT MIN(T.START_DATE) MY_DATE,
     12                 MAX(T.END_DATE),
     13                 MONTHS_BETWEEN(MAX(T.END_DATE),MIN(T.START_DATE))+1 CNT,
     14                 T.ORDER_ID,
     15                 T.PROD_ID
     16            FROM t
     17           GROUP BY T.ORDER_ID, T.PROD_ID) Q,
     18         TABLE (SELECT COLLECT(ROWNUM) FROM DUAL CONNECT BY LEVEL <= Q.CNT)
     19   ORDER BY Q.ORDER_ID, Q.PROD_ID
    SQL> /
    DDATE         ORDER_ID    PROD_ID
    ----------- ---------- ----------
    01.01.2013          12        123
    01.02.2013          12        123
    01.03.2013          12        123
    01.04.2013          12        123
    01.05.2013          12        123
    01.06.2013          12        123
    01.01.2013          12        234
    01.02.2013          12        234
    01.03.2013          12        234
    01.04.2013          12        234
    01.05.2013          12        234
    01.02.2013          13        123
    01.03.2013          13        123
    01.04.2013          13        123
    01.07.2013          13        345
    01.08.2013          13        345
    01.09.2013          13        345
    17 rows selected
    
    SQL> 
    ----
    Ramin Hashimzadeh

    Edited by: Ramin Hashimzadeh on Apr 1, 2013 12:15 PM
  • 2. Re: how can I split one row to multiple rows
    S10390 Journeyer
    Currently Being Moderated
    What about the DIFF_DATE column values...is that difference between START_DATE and END_DATE...?
  • 3. Re: how can I split one row to multiple rows
    Manik Expert
    Currently Being Moderated
    Check if this works...
      SELECT *
        FROM (    SELECT ROWNUM id,
                         TO_CHAR (ADD_MONTHS (start_date, LEVEL - 1), 'YYYYMM')
                            diff_Date,
                         order_id,
                         prod_id
                    FROM tt
              CONNECT BY     LEVEL <= MONTHS_BETWEEN (end_Date, start_date) + 1
                         AND PRIOR SYS_GUID () IS NOT NULL
                         AND id = PRIOR id)
    ORDER BY id;
    Output:
    ID     DIFF_DATE     ORDER_ID     PROD_ID
    1     201301     12     123
    2     201302     12     123
    3     201303     12     123
    4     201304     12     123
    5     201305     12     123
    6     201306     12     123
    7     201301     12     234
    8     201302     12     234
    9     201303     12     234
    10     201304     12     234
    11     201305     12     234
    12     201302     13     123
    13     201303     13     123
    14     201304     13     123
    15     201307     13     345
    16     201308     13     345
    17     201309     13     345
    Cheers,
    Manik.
  • 4. Re: how can I split one row to multiple rows
    jeneesh Guru
    Currently Being Moderated
    Note: Will fail if the dates span across multiple years..
  • 5. Re: how can I split one row to multiple rows
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    corrected ))
  • 6. Re: how can I split one row to multiple rows
    sunwill Newbie
    Currently Being Moderated
    Hi,Ramin Hashimzadeh
    GROUP BY T.ORDER_ID, T.PROD_ID) Q,
    TABLE (SELECT COLLECT(ROWNUM) FROM DUAL CONNECT BY LEVEL <= Q.CNT)

    i do not Understanding this table keyword
  • 7. Re: how can I split one row to multiple rows
    Manik Expert
    Currently Being Moderated
    May be give a try with my soluion posted above..

    Cheers,
    Manik.
  • 8. Re: how can I split one row to multiple rows
    sunwill Newbie
    Currently Being Moderated
    ok i will thanks
  • 9. Re: how can I split one row to multiple rows
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    990390 wrote:
    Hi,Ramin Hashimzadeh
    GROUP BY T.ORDER_ID, T.PROD_ID) Q,
    TABLE (SELECT COLLECT(ROWNUM) FROM DUAL CONNECT BY LEVEL <= Q.CNT)

    i do not Understanding this table keyword
    read manual
  • 10. Re: how can I split one row to multiple rows
    sunwill Newbie
    Currently Being Moderated
    thanks,it is right,my sql is
    SELECT * FROM (
    SELECT ROWNUM ID,TO_CHAR(ADD_MONTHS(BEGINDATE,LEVEL-1),'YYYYMM') SDATE,ORDER_ID,PROD_SCHM_ID FROM (
    SELECT MIN(T.BEGIN_DATE) BEGINDATE ,MAX(T.ENDDATE) ENDDATE,
    MONTHS_BETWEEN(TRUNC(MAX(T.ENDDATE) ,'MM'),TRUNC(MIN(T.BEGIN_DATE),'MM')) CNT,
    T.ORDER_ID,T.PROD_SCHM_ID FROM (
    SELECT M.OI_ID,M.ORDER_ID,M.BEGIN_DATE,NVL(M.END_DATE,TO_DATE(M.OI_DET_CRE_YM,'YYYYMM')) ENDDATE,M.PROD_SCHM_ID FROM T M
    WHERE M.ORDER_ID IN (10477,31602,13295)
    ) T GROUP BY T.ORDER_ID,T.PROD_SCHM_ID ) TT CONNECT BY LEVEL<=MONTHS_BETWEEN(ENDDATE,BEGINDATE)+2
    AND PRIOR SYS_GUID () IS NOT NULL AND ORDER_ID=PRIOR ORDER_ID AND PROD_SCHM_ID=PRIOR PROD_SCHM_ID) ORDER BY ORDER_ID,PROD_SCHM_ID,SDATE

    Edited by: sunwill on 2013-4-1 上午7:04

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points