| Split 1 row into multiple based on date [message #628546] |
Wed, 26 November 2014 03:42  |
 |
sinpeak
Messages: 59
Registered: January 2011
Location: india
|
Member |
|
|
I have a table with the following structure :
Table name : RT
Columns :
RT_ID NUMBER(9) NOT NULL
START_DATE DATE NOT NULL
END_DATE DATE NOT NULL
PARAM_1 VARCHAR2(20)
PARAM_2 VARCHAR2(20)
PARAM_3 VARCHAR2(20)
PARAM_4 VARCHAR2(20)
I need to split each row into multiple rows based on the START_DATE and END_DATE range.
Example if one row in RT is :
RT_ID: 82
START_DATE: 01-JAN-2014
END_DATE: 04-JAN-2014
PARAM_1: A
PARAM_2: B
PARAM_3: C
PARAM_4: D
Then it should appear as :
82 1-Jan-14 A B C D
82 2-Jan-14 A B C D
82 3-Jan-14 A B C D
82 4-Jan-14 A B C D
Please advise/suggest how can this be achieved.
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: Split 1 row into multiple based on date [message #628559 is a reply to message #628546] |
Wed, 26 November 2014 05:19  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
How about this:
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 26 11:18:12 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL>
SQL> col param_1 for a5;
SQL> col param_2 for a5;
SQL> col param_3 for a5;
SQL> col param_4 for a5;
SQL> col param_5 for a5;
SQL>
SQL>
SQL> create table rt (
2 RT_ID NUMBER(9) NOT NULL,
3 START_DATE DATE NOT NULL,
4 END_DATE DATE NOT NULL,
5 PARAM_1 VARCHAR2(20) ,
6 PARAM_2 VARCHAR2(20),
7 PARAM_3 VARCHAR2(20),
8 PARAM_4 VARCHAR2(20));
Table created.
SQL> --
SQL> insert into rt values (82,'01-JAN-2014','04-JAN-2014','A','B','C','
1 row created.
SQL> commit;
Commit complete.
SQL> --
SQL>
SQL> select * from rt;
RT_ID START_DAT END_DATE PARAM PARAM PARAM PARAM
---------- --------- --------- ----- ----- ----- -----
82 01-JAN-14 04-JAN-14 A B C D
SQL>
SQL> --
SQL> create table rt_res (
2 RT_ID NUMBER(9) NOT NULL,
3 RES_DATE DATE NOT NULL,
4 PARAM_1 VARCHAR2(20) ,
5 PARAM_2 VARCHAR2(20),
6 PARAM_3 VARCHAR2(20),
7 PARAM_4 VARCHAR2(20));
Table created.
SQL>
SQL> --
SQL>
SQL> declare
2 cnt_days number(6);
3 begin
4 for x in (select * from rt) loop
5 select x.end_date - x.start_date + 1 into cnt_days from dual;
6 for i in 1 .. cnt_days loop
7 insert into rt_res
8 values
9 (x.rt_id,
10 x.start_date + i - 1,
11 x.param_1,
12 x.param_2,
13 x.param_3,
14 x.param_4);
15 end loop;
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> --
SQL> commit;
Commit complete.
SQL> --
SQL> select * from rt_res;
RT_ID RES_DATE PARAM PARAM PARAM PARAM
---------- --------- ----- ----- ----- -----
82 01-JAN-14 A B C D
82 02-JAN-14 A B C D
82 03-JAN-14 A B C D
82 04-JAN-14 A B C D
Regards,
Andrey R.
[Updated on: Wed, 26 November 2014 05:19] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Split 1 row into multiple based on date [message #628805 is a reply to message #628795] |
Fri, 28 November 2014 09:15 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Hello Rajesh,
Welcome to the forum. Good to see you want tk help OP, however, it would be nice if you follow posting guidelines. You need to use code tags, and it would be good if you add an explanation about your suggestion.
|
|
|
|
| Re: Split 1 row into multiple based on date [message #628814 is a reply to message #628795] |
Fri, 28 November 2014 09:54 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Rajesh Reddy wrote on Fri, 28 November 2014 15:38Dear sinpeak,
go thrugh the below query
Select rt_id,start_Date+level-1,param_1,param_2,param_3,param_4
from tablename
connect by level<=(select (trunc(end_date)-trunc(start_DAte))+1 from tablename);
Thanks
This query will not work as soon as the table has more than one row.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
