Asktom Oracle:How to multiplex single row into multiple rows

Hi Tom,

First of all, thanks for your tremendous contribution to the Oracle Community in helping people like us solve day to day Oracle replated problems.

I have a small problem. Here are the details:

CREATE TABLE T
(
HS_ID NUMBER(20) PRIMARY KEY,
HS_NM VARCHAR2(30 BYTE),
HS_STRT_DT DATE,
HS_END_DT DATE,
HS_CLT_IND CHAR(1 BYTE)
);

Insert into TMS.T
   (HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
 Values
   (1, 'Alaska', TO_DATE('01/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into TMS.T
   (HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
 Values
   (2, 'Alabama', TO_DATE('01/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
Insert into TMS.T
   (HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
 Values
   (3, 'Virginia', TO_DATE('01/01/2001 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into TMS.T
   (HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
 Values
   (4, 'Virginia', TO_DATE('01/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
COMMIT;
Here is the business rule:

For each row in table t, view should traslate it into multiple rows based on start, end date and current year (HS_STRT_DT , HS_END_DT).

Taking example of row with id=1:
strt_dt=01/01/2004
end_dt=12/31/2012
current_year=2007

If current_year <= end_dt then the row should get translated into 4 rows, one for each year starting 2004 and ending at 2007.
If current_year > end_dt then that particular row should have (year(end_dt)-strt_dt) rows, starting at year(strt_dt) and ending at year(strt_dt)


Thanks in advance for your time and help.

and we said...

You might have to play around with some boundary value conditions - just to validate that the math is right - don't know your data like you know it, but here is the idea, I used 100 as the max year spread, you can adjust based on your needs

ops$tkyte%ORA10GR2> select * from t;

     HS_ID HS_NM                          HS_STRT_D HS_END_DT H
---------- ------------------------------ --------- --------- -
         1 Alaska                         01-JAN-04 31-DEC-12 N
         2 Alabama                        01-JAN-03 31-DEC-12 Y
         3 Virginia                       01-JAN-01 31-DEC-04 Y
         4 Virginia                       01-JAN-05 31-DEC-12 N

WITH data
     AS (    SELECT LEVEL - 1 l
               FROM DUAL
         CONNECT BY LEVEL <= 100)
  SELECT hs_id,
         hs_nm,
         TO_CHAR (ADD_MONTHS (hs_strt_dt, l * 12), 'yyyy'),
         hs_clt_ind
    FROM t, data
   WHERE l <
            CEIL (MONTHS_BETWEEN (LEAST (SYSDATE, hs_end_dt), hs_strt_dt) / 12)
         AND TO_CHAR (ADD_MONTHS (hs_strt_dt, l * 12), 'yyyy') <=
                TO_CHAR (SYSDATE, 'yyyy')
ORDER BY 1, 2, 3
     HS_ID HS_NM                          TO_C H
---------- ------------------------------ ---- -
         1 Alaska                         2004 N
         1 Alaska                         2005 N
         1 Alaska                         2006 N
         1 Alaska                         2007 N
         2 Alabama                        2003 Y
         2 Alabama                        2004 Y
         2 Alabama                        2005 Y
         2 Alabama                        2006 Y
         2 Alabama                        2007 Y
         3 Virginia                       2001 Y
         3 Virginia                       2002 Y
         3 Virginia                       2003 Y
         3 Virginia                       2004 Y
         4 Virginia                       2005 N
         4 Virginia                       2006 N
         4 Virginia                       2007 N

16 rows selected.