Skip to Main Content
  • Questions

  • How to multiplex single row into multiple rows

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dinanath.

Asked: February 09, 2007 - 12:08 pm UTC

Last updated: January 10, 2012 - 10:28 pm UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

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 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/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');

INSERT INTO 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/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
COMMIT;

SELECT * FROM T;
HS_ID HS_NM HS_STRT_DT HS_END_DT H
-------- ------------------------------ ------------------- ------------------- -
1 Alaska 2004-01-01 00:00:00 4712-12-31 00:00:00 N
2 Alabama 2003-01-01 00:00:00 4712-12-31 00:00:00 Y

As one can see this table is having two rows with id 1 and 2. I want to build a view, say v, on this table whose definition would be as below:

HS_ID =>t.HS_ID
HS_NM =>t.HS_NM,
YEAR => Derived
HOLIDAY_FLAG=>t.HS_CLT_IND

the view based on above table, t, should give result as below:

select HS_ID,HS_NM,YEAR, HOLIDAY_FLAG from v;
HS_ID HS_NM YEAR FLAG
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


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/4712
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.

Regards,
Dina

and Tom 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

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
  2  as
  3  (select level-1 l from dual connect by level <= 100)
  4  select hs_id,
  5         hs_nm,
  6         to_char(add_months(hs_strt_dt,l*12),'yyyy'),
  7             hs_clt_ind
  8    from t, data
  9   where l < ceil(months_between(least(sysdate,hs_end_dt),hs_strt_dt)/12)
 10     and to_char(add_months(hs_strt_dt,l*12),'yyyy') <= to_char(sysdate,'yyyy')
 11   order by 1, 2, 3
 12  /

     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.

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dinanath Gupt, February 09, 2007 - 12:57 pm UTC

You're genius!
Thanks a ton!

An alternative SQL solution

Frank Zhou, February 09, 2007 - 5:32 pm UTC

Here is an alternative SQL solution .

Thanks,

Frank

SQL>
SQL> select hs_id, hs_nm,
2 to_char(add_months(hs_strt_dt,(LEVEL-1)*12),'yyyy') Year,
3 hs_clt_ind
4 from t
5 connect by PRIOR hs_id = hs_id
6 and PRIOR hs_nm = hs_nm
7 and level <= ceil(months_between( least(sysdate,hs_end_dt), hs_strt_dt)/12)
8 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
9 order by 1, 2, 3;

HS_ID HS_NM YEAR 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

9 rows selected.

SQL>

With Statement

Notna, February 10, 2007 - 1:31 am UTC

Hi Tom,

Just want to check with you if the "With Statement" is also available in PL/SQL, if not, then what would be the alternative approach for this type of query?

Thanks.

Regards,
Notna
Tom Kyte
February 12, 2007 - 9:44 am UTC

with is available in plsql

A reader, February 12, 2007 - 3:27 am UTC

Tom,

I guess your logic on Dual depends on its SINGlE row, when I do the following the Connect By clause goes into a Loop. It keeps getting the SAME level over and over again for the FIRST row it gets.

I take it its something to do with the Parent itself can be self-connected, but why over and over again?

create table dual2 as select * from dual

insert into dual2 select * from dual

update dual2 set dummy = 'Y' where rownum =1

select dummy,level-1 l from dual2 connect by level <= 100

Generating Data

Priya, February 12, 2007 - 8:37 pm UTC

Hi Tom,

I want to generate rolling forward data for the next 12 months based on the last business day of a particular month. For instance, if the last business day is 11/28/2006, I want to generate data for the next 12 months ie till nov of 2007.

Here are the table scripts with some sample data

Create table test_Tbl(
SCENARIO   VARCHAR2 (10) 
MONTH      VARCHAR2 (2) 
YEAR       VARCHAR2 (4) 
CURRENCY   VARCHAR2 (3) 
RATE       NUMBER (20,6) 
ENTRY_DATE DATE 
);

Insert into test_tbl values('Test','11','2006','USD',20.6,'28-NOV-2006');
Insert into test_tbl values('Test','11','2006','EUR',20.6,'28-NOV-2006');


What I want is data for the next 12 months for the 2 currencies with the rate and entrydates same with only the values for month and year changing accordingly.
The data should look like this for the currency USD and similarly for EUR

SCENARIO MONTH YEAR CURRENCY RATE ENTRY_DATE

Test 11 2006 USD 20.6 11/28/2006
Test 12 2006 USD 20.6 11/28/2006
Test 01 2007 USD 20.6 11/28/2006
Test 02 2007 USD 20.6 11/28/2006
Test 03 2007 USD 20.6 11/28/2006
Test 04 2007 USD 20.6 11/28/2006
Test 05 2007 USD 20.6 11/28/2006
Test 06 2007 USD 20.6 11/28/2006
Test 07 2007 USD 20.6 11/28/2006
Test 08 2007 USD 20.6 11/28/2006
Test 09 2007 USD 20.6 11/28/2006
Test 10 2007 USD 20.6 11/28/2006
Test 11 2007 USD 20.6 11/28/2006


As always, appreciate your valuble advice and suggestions.

Thanks,
Priya.

Tom Kyte
February 13, 2007 - 9:48 am UTC

why in the world would you do that month year bit of stuff???? man oh man.

So, my suggestion:

lose month/year, have a single date column!!! please!!!!



ops$tkyte%ORA10GR2> insert into test_tbl
  2  with data
  3  as
  4  (select level l from dual connect by level <= 12)
  5  select scenario,
  6         to_char( add_months( to_date(month||'-'||year,'mm-yyyy'), l ), 'mm' ),
  7         to_char( add_months( to_date(month||'-'||year,'mm-yyyy'), l ), 'yyyy' ),
  8         currency,
  9         rate,
 10         entry_date
 11    from (select *
 12            from test_tbl
 13           where scenario = 'Test'
 14             and (entry_date,currency) in
 15                         (select max(entry_date), currency
 16                    from test_tbl
 17                   where scenario = 'Test'
 18                                   group by currency )
 19         ), data
 20  /

24 rows created.

ops$tkyte%ORA10GR2> select * from test_tbl
  2  order by currency, to_date(month||'-'||year,'mm-yyyy')
  3  /

SCENARIO   MO YEAR CUR       RATE ENTRY_DAT
---------- -- ---- --- ---------- ---------
Test       11 2006 EUR       20.6 28-NOV-06
Test       12 2006 EUR       20.6 28-NOV-06
Test       01 2007 EUR       20.6 28-NOV-06
Test       02 2007 EUR       20.6 28-NOV-06
Test       03 2007 EUR       20.6 28-NOV-06
Test       04 2007 EUR       20.6 28-NOV-06
Test       05 2007 EUR       20.6 28-NOV-06
Test       06 2007 EUR       20.6 28-NOV-06
Test       07 2007 EUR       20.6 28-NOV-06
Test       08 2007 EUR       20.6 28-NOV-06
Test       09 2007 EUR       20.6 28-NOV-06
Test       10 2007 EUR       20.6 28-NOV-06
Test       11 2007 EUR       20.6 28-NOV-06
Test       11 2006 USD       20.6 28-NOV-06
Test       12 2006 USD       20.6 28-NOV-06
Test       01 2007 USD       20.6 28-NOV-06
Test       02 2007 USD       20.6 28-NOV-06
Test       03 2007 USD       20.6 28-NOV-06
Test       04 2007 USD       20.6 28-NOV-06
Test       05 2007 USD       20.6 28-NOV-06
Test       06 2007 USD       20.6 28-NOV-06
Test       07 2007 USD       20.6 28-NOV-06
Test       08 2007 USD       20.6 28-NOV-06
Test       09 2007 USD       20.6 28-NOV-06
Test       10 2007 USD       20.6 28-NOV-06
Test       11 2007 USD       20.6 28-NOV-06

26 rows selected.

Dinanath Gupt, February 13, 2007 - 12:51 am UTC

Assuming ENTRY_DATE holds the last business day of the month(it could be any other column in the table or a derived value)and (SCENARIO,MONTH,YEAR,CURRENCY) uniquely identifies a row in the above table(well, table can be PK based and in that case just PK can be substituted in connect clause instead of all these columns), here is the query which you might be looking for :

SELECT SCENARIO,TO_CHAR(ADD_MONTHS(ENTRY_DATE,LEVEL-1),'MM') MONTH, TO_CHAR(ADD_MONTHS(ENTRY_DATE,LEVEL-1),'YYYY') YEAR , CURRENCY,RATE,ENTRY_DATE
FROM
TEST_TBL A CONNECT BY PRIOR SCENARIO = SCENARIO AND PRIOR MONTH=MONTH AND PRIOR YEAR =YEAR AND PRIOR CURRENCY = CURRENCY AND LEVEL <=13 AND PRIOR DBMS_RANDOM.STRING ('P', 10) IS NOT NULL
;


Example:

SQL> DESC TEST_TBL;
Name Null? Type
----------------------------- -------- --------------------
SCENARIO VARCHAR2(10)
MONTH VARCHAR2(2)
YEAR VARCHAR2(4)
CURRENCY VARCHAR2(3)
RATE NUMBER(20,6)
ENTRY_DATE DATE

SQL> SELECT * FROM TEST_TBL;

SCENARIO MO YEAR CUR RATE ENTRY_DATE
---------- -- ---- --- ---------- ----------
Test 11 2006 USD 20.6 11/28/2006
Test 11 2006 EUR 20.6 11/28/2006

SQL> SELECT SCENARIO,TO_CHAR(ADD_MONTHS(ENTRY_DATE,LEVEL-1),'MM') MONTH, TO_CHAR(ADD_MONTHS(ENTRY_DATE,LEVEL-1),'YYYY') YEAR , CURRENCY,RATE,ENTRY_DATE
2 FROM
3 TEST_TBL A CONNECT BY PRIOR SCENARIO = SCENARIO AND PRIOR MONTH=MONTH AND PRIOR YEAR =YEAR AND PRIOR CURRENCY = CURRENCY AND LEVEL <=13 AND PRIOR DBMS_RANDOM.STRING ('P', 10) IS NOT NULL
4 /

SCENARIO MO YEAR CUR RATE ENTRY_DATE
---------- -- ---- --- ---------- ----------
Test 11 2006 EUR 20.6 11/28/2006
Test 12 2006 EUR 20.6 11/28/2006
Test 01 2007 EUR 20.6 11/28/2006
Test 02 2007 EUR 20.6 11/28/2006
Test 03 2007 EUR 20.6 11/28/2006
Test 04 2007 EUR 20.6 11/28/2006
Test 05 2007 EUR 20.6 11/28/2006
Test 06 2007 EUR 20.6 11/28/2006
Test 07 2007 EUR 20.6 11/28/2006
Test 08 2007 EUR 20.6 11/28/2006
Test 09 2007 EUR 20.6 11/28/2006
Test 10 2007 EUR 20.6 11/28/2006
Test 11 2007 EUR 20.6 11/28/2006

Test 11 2006 USD 20.6 11/28/2006
Test 12 2006 USD 20.6 11/28/2006
Test 01 2007 USD 20.6 11/28/2006
Test 02 2007 USD 20.6 11/28/2006
Test 03 2007 USD 20.6 11/28/2006
Test 04 2007 USD 20.6 11/28/2006
Test 05 2007 USD 20.6 11/28/2006
Test 06 2007 USD 20.6 11/28/2006
Test 07 2007 USD 20.6 11/28/2006
Test 08 2007 USD 20.6 11/28/2006
Test 09 2007 USD 20.6 11/28/2006
Test 10 2007 USD 20.6 11/28/2006
Test 11 2007 USD 20.6 11/28/2006

26 rows selected.

SQL>

Regards,
Dina

The With clause - performance

A reader, February 13, 2007 - 8:22 am UTC

Hi Tom,
Is there any advantage performance-wise to using the With clause? Other than making it easier to write, what are the uses of this clause?

Thanks in advance


Tom Kyte
February 13, 2007 - 10:02 am UTC

"it depends"

it can be useful with the same query is referenced many times in a single sql statement (we can materialize it and reuse the results)

it can be useful from a 'writing complex sql' perspective as it permits you to build a "modular" query.

Thanks a ton!!

Priya, February 13, 2007 - 1:34 pm UTC

Thanks Tom and Dina.

Exactly what I wanted. Thanks a ton..

Tom, will surely be looking into converting the month and year to a single date column.

Thanks as always..

Hi

Siva, February 14, 2007 - 7:04 am UTC

Hi all,
pls let me know why do we use
DBMS_RANDOM.STRING ('P', 10) IS NOT NULL
in this query..is it that it will always evaluate to true and why do we chk for not null. if i remove the same, i'm hitting the oracle error "user loop data".

SELECT SCENARIO,TO_CHAR(ADD_MONTHS(ENTRY_DATE,LEVEL-1),'MM') MONTH, TO_CHAR(ADD_MONTHS(ENTRY_DATE,LEVEL-1),'YYYY') YEAR , CURRENCY,RATE,ENTRY_DATE
FROM
TEST_TBL A CONNECT BY PRIOR SCENARIO = SCENARIO AND PRIOR MONTH=MONTH AND PRIOR YEAR =YEAR AND PRIOR CURRENCY = CURRENCY AND LEVEL <=13 AND PRIOR DBMS_RANDOM.STRING ('P', 10) IS NOT NULL

Re: why do we use DBMS_RANDOM.STRING ('P', 10) IS NOT NULL in this query

Frank Zhou, February 14, 2007 - 11:33 am UTC

Siva,

You have already answered your own question.

(Without it , you will encounter oracle error "user loop data". )

Frank


THANKS

Siva, February 15, 2007 - 2:01 am UTC

Hi Frank,
thanks..but one more doubt... can we use any other chk which evaluates to true like "1=1" and not
DBMS_RANDOM.STRING ('P', 10) IS NOT NULL

if any other condition can be used, is there a generic rule for that..?

Sam, March 30, 2007 - 5:47 am UTC

Answer to Priya
Alternative Query to insert rows

SELECT scenario,
TO_CHAR (ADD_MONTHS (TO_DATE (MONTH || '-' || YEAR, 'mm-yyyy'),
l_dual.l
),
'mm'
) MONTH,
TO_CHAR (ADD_MONTHS (TO_DATE (MONTH || '-' || YEAR, 'mm-yyyy'),
l_dual.l
),
'yyyy'
) YEAR,
currency, rate, entry_date
FROM (SELECT scenario, MONTH, YEAR, currency, rate, entry_date
FROM test_tbl
WHERE scenario = 'Test') scen,
(SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <= 12) l_dual

please solve this scenario

charan, August 01, 2011 - 7:28 am UTC

Hi Tom,

please suggest this scenario..............
Write a Quary?
Source Target
Empid deptno empid deptno dcount
1 A 1 A 3
2 B 2 B 2
3 A 3 A 3
4 C 4 C 1
5 B 5 B 2
6 A 6 A 3

Tom Kyte
August 01, 2011 - 12:00 pm UTC

please, give me a break.


no clue, not a single inkling of a clue, what you mean.

how to pick the particular row in different departments within same table ?

charankumar, January 10, 2012 - 12:52 am UTC

Hi tom,

i have been facing this Problem for last one week.please give suggestion on that.........


i have tabel with different departments but i need to pick the 3rd record in every department.please give a solution for this problem.
Tom Kyte
January 10, 2012 - 10:28 pm UTC

define what it means to be the "3rd" record in a department for me first. I don't know how to see the 3rd record in a department

where is the table
where are the inserts
where are the specifications telling me how to SORT the data so I can see the 3rd record?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.