10 Replies Latest reply: Apr 1, 2013 9:05 AM by sunwill RSS

    how can I split one row to multiple rows

    sunwill
      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
          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
            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
              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
                Note: Will fail if the dates span across multiple years..
                • 6. Re: how can I split one row to multiple rows
                  sunwill
                  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
                    May be give a try with my soluion posted above..

                    Cheers,
                    Manik.
                    • 8. Re: how can I split one row to multiple rows
                      sunwill
                      ok i will thanks
                      • 9. Re: how can I split one row to multiple rows
                        Ramin Hashimzadeh
                        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
                          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