Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows by generating date based on column value (12.1.0.1.0)
| Duplicate rows by generating date based on column value [message #686893] |
Mon, 30 January 2023 03:54  |
 |
OraFerro
Messages: 415
Registered: July 2011
|
Senior Member |
|
|
Hi All,
I need to generate 2 rows for every existing row in my data. Each row contains 2 month numbers and a year. I need each of the generated rows to hold a data = 01-one of the month numbers- the year.
Following is my example:
create table testc
(
id number,
fist_mon number,
second_mon number,
dyear number
);
insert all
into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (1, 1, 7, 2000)
into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (1, 5, 11, 2001)
into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (1, 2, 8, 2002)
into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (2, 3, 9, 2005)
into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (2, 1, 7, 2007)
into TESTC(id, FIST_MON, SECOND_MON,DYEAR) values (3, 4, 10, 2022)
select * from dual;
select * from testc;
ID FIST_MON SECOND_MON DYEAR
1 1 7 2000
1 5 11 2001
1 2 8 2002
2 3 9 2005
2 1 7 2007
3 4 10 2022
--- what I want to get after adding the generated_date is:
ID FIST_MON SECOND_MON DYEAR generated_date
1 1 7 2000 1-1-2000
1 1 7 2000 1-7-2000
1 5 11 2001 01-05-2001
1 5 11 2001 01-11-2001
1 2 8 2002 01-02-2002
1 2 8 2002 01-08-2002
2 3 9 2005 01-03-2005
2 3 9 2005 01-09-2005
2 1 7 2007 01-01-2007
2 1 7 2007 01-07-2007
3 4 10 2022 01-04-2022
3 4 10 2022 01-10-2022
Ferro
|
|
|
|
|
|
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686899 is a reply to message #686896] |
Mon, 30 January 2023 05:30  |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Union all solution is less efficient since it scans table twice. We can use lateral/cross apply:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'
/
SELECT T.*,
L.GENERATED_DATE
FROM TESTC T,
LATERAL(
SELECT TO_DATE(
CASE LEVEL
WHEN 1 THEN '01-' || LPAD(FIRST_MON,2,'0') || '-' || DYEAR
ELSE '01-' || LPAD(SECOND_MON,2,'0') || '-' || DYEAR
END,
'DD-MM-YYYY'
) GENERATED_DATE
FROM DUAL
CONNECT BY LEVEL <= 2
) L
/
ID FIRST_MON SECOND_MON DYEAR GENERATED_
---------- ---------- ---------- ---------- ----------
1 1 7 2000 01-01-2000
1 1 7 2000 01-07-2000
1 5 11 2001 01-05-2001
1 5 11 2001 01-11-2001
1 2 8 2002 01-02-2002
1 2 8 2002 01-08-2002
2 3 9 2005 01-03-2005
2 3 9 2005 01-09-2005
2 1 7 2007 01-01-2007
2 1 7 2007 01-07-2007
3 4 10 2022 01-04-2022
3 4 10 2022 01-10-2022
12 rows selected.
SQL>
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686900 is a reply to message #686896] |
Mon, 30 January 2023 05:30 |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Union all solution is less efficient since it scans table twice. We can use lateral/cross apply:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'
/
SELECT T.*,
L.GENERATED_DATE
FROM TESTC T,
LATERAL(
SELECT TO_DATE(
CASE LEVEL
WHEN 1 THEN '01-' || LPAD(FIRST_MON,2,'0') || '-' || DYEAR
ELSE '01-' || LPAD(SECOND_MON,2,'0') || '-' || DYEAR
END,
'DD-MM-YYYY'
) GENERATED_DATE
FROM DUAL
CONNECT BY LEVEL <= 2
) L
/
ID FIRST_MON SECOND_MON DYEAR GENERATED_
---------- ---------- ---------- ---------- ----------
1 1 7 2000 01-01-2000
1 1 7 2000 01-07-2000
1 5 11 2001 01-05-2001
1 5 11 2001 01-11-2001
1 2 8 2002 01-02-2002
1 2 8 2002 01-08-2002
2 3 9 2005 01-03-2005
2 3 9 2005 01-09-2005
2 1 7 2007 01-01-2007
2 1 7 2007 01-07-2007
3 4 10 2022 01-04-2022
3 4 10 2022 01-10-2022
12 rows selected.
SQL>
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686902 is a reply to message #686899] |
Mon, 30 January 2023 09:21 |
 |
mathguy
Messages: 41
Registered: January 2023
|
Member |
|
|
We can use UNPIVOT, but the code is a bit ugly since you want to repeat the input columns in the output, you don't just want the generated dates. The code is simpler if we use the poor man's way of unpivoting (the way the operation was done before Oracle 11 - that is, before UNPIVOT was introduced), with a Cartesian join. As a side note - "first" is spelled with an "r" in the middle; "fist" means something else.
select id, fist_mon, second_mon, dyear,
to_date(to_char(dyear, '0000') || to_char(case flag when 1 then fist_mon else second_mon end, '00'), 'yyyymm') as generated_date
from testc cross join (select 1 as flag from dual union all select 2 from dual)
order by id, generated_date -- if needed
;
ID FIST_MON SECOND_MON DYEAR GENERATED_DATE
-- ---------- ---------- ----- ---------------
1 1 7 2000 01-01-2000
1 1 7 2000 01-07-2000
1 5 11 2001 01-05-2001
1 5 11 2001 01-11-2001
1 2 8 2002 01-02-2002
1 2 8 2002 01-08-2002
2 3 9 2005 01-09-2005
2 1 7 2007 01-01-2007
2 1 7 2007 01-07-2007
3 4 10 2022 01-04-2022
3 4 10 2022 01-10-2022 |
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686903 is a reply to message #686902] |
Mon, 30 January 2023 09:29 |
|
mathguy
Messages: 41
Registered: January 2023
|
Member |
|
|
An UNPIVOT solution might look like this:
with prep as (select id, fist_mon, second_mon, dyear, fist_mon as f, second_mon as s from testc)
select id, fist_mon, second_mon, dyear, to_date(to_char(dyear, '0000') || to_char(mon, '00'), 'yyyymm') as generated_date
from prep
unpivot (mon for name in (f as 'first', s as 'second'))
order by id, generated_date -- or whatever is needed
; |
|
|
|
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686911 is a reply to message #686907] |
Tue, 31 January 2023 01:58 |
|
OraFerro
Messages: 415
Registered: July 2011
|
Senior Member |
|
|
@mathguy
Quote:The "lateral" thing is called a lateral join
Of course, what I meant is that it avoids the obvious union all by repeating the base table select as in the above examples.
I am trying to use MATCH_RECOGNIZE after the below failed without connect by or join all:
SELECT T.*,
TO_DATE(
CASE rn
WHEN 1 THEN '01-' || LPAD(fist_mon, 2, '0') || '-' || dyear
ELSE '01-' || LPAD(second_mon, 2, '0') || '-' || dyear
END,
'DD-MM-YYYY'
) AS generated_date
FROM (
SELECT T.*,
ROW_NUMBER() OVER (PARTITION BY id, fist_mon, second_mon, dyear ORDER BY fist_mon) AS rn
FROM TESTC T
) T;
WITH testc_data AS (
SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
)
SELECT *
FROM testc_data
MATCH_RECOGNIZE (
ORDER BY id, dyear
MEASURES
FIRST(fist_mon) as fist_mon,
FIRST(second_mon) as second_mon,
FIRST(dyear) as dyear,
TO_DATE('01-' || LPAD(FIRST(fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (A B)
DEFINE
A AS fist_mon <= second_mon,
B AS fist_mon > second_mon
)
UNION ALL
SELECT *
FROM testc
MATCH_RECOGNIZE (
ORDER BY id, dyear
MEASURES
FIRST(fist_mon) as fist_mon,
FIRST(second_mon) as second_mon,
FIRST(dyear) as dyear,
TO_DATE('01-' || LPAD(FIRST(second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (A B)
DEFINE
A AS fist_mon <= second_mon,
B AS fist_mon > second_mon
);
|
|
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686913 is a reply to message #686911] |
Tue, 31 January 2023 03:00 |
 |
Barbara Boehmer
Messages: 8990
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is just enough modification to your combination of union all and match_recognize to make it return the correct results and remove some things that seemed unnecessary, but probably not an efficient method.
SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
2 SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
3 SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
4 SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
5 SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
6 SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
7 SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
8 )
9 SELECT id, fist_mon, second_mon, dyear, generated_date
10 FROM testc_data
11 MATCH_RECOGNIZE (
12 ORDER BY id, dyear
13 MEASURES
14 TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
15 ALL ROWS PER MATCH
16 PATTERN (A)
17 DEFINE
18 A AS fist_mon <= second_mon)
19 UNION ALL
20 SELECT id, fist_mon, second_mon, dyear, generated_date
21 FROM testc_data
22 MATCH_RECOGNIZE (
23 ORDER BY id, dyear
24 MEASURES
25 TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
26 ALL ROWS PER MATCH
27 PATTERN (B)
28 DEFINE
29 B AS fist_mon <= second_mon)
30 ORDER BY generated_date
31 /
ID FIST_MON SECOND_MON DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
1 1 7 2000 Sat 01-Jan-2000
1 1 7 2000 Sat 01-Jul-2000
1 5 11 2001 Tue 01-May-2001
1 5 11 2001 Thu 01-Nov-2001
1 2 8 2002 Fri 01-Feb-2002
1 2 8 2002 Thu 01-Aug-2002
2 3 9 2005 Tue 01-Mar-2005
2 3 9 2005 Thu 01-Sep-2005
2 1 7 2007 Mon 01-Jan-2007
2 1 7 2007 Sun 01-Jul-2007
3 4 10 2022 Fri 01-Apr-2022
3 4 10 2022 Sat 01-Oct-2022
12 rows selected.
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686914 is a reply to message #686913] |
Tue, 31 January 2023 03:08 |
|
Barbara Boehmer
Messages: 8990
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Just to be clear, the following produces the same result as the above, without the match_recognize.
SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
2 SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
3 SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
4 SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
5 SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
6 SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
7 SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
8 )
9 SELECT id, fist_mon, second_mon, dyear,
10 TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
11 FROM testc_data
12 UNION ALL
13 SELECT id, fist_mon, second_mon, dyear,
14 TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY') as generated_date
15 FROM testc_data
16 ORDER BY generated_date
17 /
ID FIST_MON SECOND_MON DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
1 1 7 2000 Sat 01-Jan-2000
1 1 7 2000 Sat 01-Jul-2000
1 5 11 2001 Tue 01-May-2001
1 5 11 2001 Thu 01-Nov-2001
1 2 8 2002 Fri 01-Feb-2002
1 2 8 2002 Thu 01-Aug-2002
2 3 9 2005 Tue 01-Mar-2005
2 3 9 2005 Thu 01-Sep-2005
2 1 7 2007 Mon 01-Jan-2007
2 1 7 2007 Sun 01-Jul-2007
3 4 10 2022 Fri 01-Apr-2022
3 4 10 2022 Sat 01-Oct-2022
12 rows selected.
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686915 is a reply to message #686914] |
Tue, 31 January 2023 03:48 |
|
Barbara Boehmer
Messages: 8990
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is another variation, using match_recognize without union all.
SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
2 SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
3 SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
4 SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
5 SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
6 SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
7 SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
8 )
9 SELECT id, fist_mon, second_mon, dyear, generated_date
10 FROM (SELECT id, fist_mon, second_mon, dyear, lvl
11 FROM testc_data,
12 (SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= 2))
13 MATCH_RECOGNIZE (
14 ORDER BY id, dyear
15 MEASURES
16 DECODE (lvl,
17 1, TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY'),
18 2, TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY')) as generated_date
19 ALL ROWS PER MATCH
20 PATTERN (A)
21 DEFINE
22 A AS fist_mon <= second_mon)
23 ORDER BY generated_date
24 /
ID FIST_MON SECOND_MON DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
1 1 7 2000 Sat 01-Jan-2000
1 1 7 2000 Sat 01-Jul-2000
1 5 11 2001 Tue 01-May-2001
1 5 11 2001 Thu 01-Nov-2001
1 2 8 2002 Fri 01-Feb-2002
1 2 8 2002 Thu 01-Aug-2002
2 3 9 2005 Tue 01-Mar-2005
2 3 9 2005 Thu 01-Sep-2005
2 1 7 2007 Mon 01-Jan-2007
2 1 7 2007 Sun 01-Jul-2007
3 4 10 2022 Fri 01-Apr-2022
3 4 10 2022 Sat 01-Oct-2022
12 rows selected.
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686916 is a reply to message #686915] |
Tue, 31 January 2023 03:51 |
|
Barbara Boehmer
Messages: 8990
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Similarly, the following produces the same results as above.
SCOTT@orcl_12.1.0.2.0> WITH testc_data AS (
2 SELECT 1 id, 1 fist_mon, 7 second_mon, 2000 dyear FROM DUAL UNION ALL
3 SELECT 1 id, 5 fist_mon, 11 second_mon, 2001 dyear FROM DUAL UNION ALL
4 SELECT 1 id, 2 fist_mon, 8 second_mon, 2002 dyear FROM DUAL UNION ALL
5 SELECT 2 id, 3 fist_mon, 9 second_mon, 2005 dyear FROM DUAL UNION ALL
6 SELECT 2 id, 1 fist_mon, 7 second_mon, 2007 dyear FROM DUAL UNION ALL
7 SELECT 3 id, 4 fist_mon, 10 second_mon, 2022 dyear FROM DUAL
8 )
9 SELECT id, fist_mon, second_mon, dyear,
10 DECODE (lvl,
11 1, TO_DATE('01-' || LPAD((fist_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY'),
12 2, TO_DATE('01-' || LPAD((second_mon), 2, '0') || '-' || dyear, 'DD-MM-YYYY')) as generated_date
13 FROM testc_data,
14 (SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= 2)
15 ORDER BY generated_date
16 /
ID FIST_MON SECOND_MON DYEAR GENERATED_DATE
---------- ---------- ---------- ---------- ---------------
1 1 7 2000 Sat 01-Jan-2000
1 1 7 2000 Sat 01-Jul-2000
1 5 11 2001 Tue 01-May-2001
1 5 11 2001 Thu 01-Nov-2001
1 2 8 2002 Fri 01-Feb-2002
1 2 8 2002 Thu 01-Aug-2002
2 3 9 2005 Tue 01-Mar-2005
2 3 9 2005 Thu 01-Sep-2005
2 1 7 2007 Mon 01-Jan-2007
2 1 7 2007 Sun 01-Jul-2007
3 4 10 2022 Fri 01-Apr-2022
3 4 10 2022 Sat 01-Oct-2022
12 rows selected.
[Updated on: Tue, 31 January 2023 03:57] Report message to a moderator |
|
|
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686919 is a reply to message #686911] |
Tue, 31 January 2023 14:08 |
|
mathguy
Messages: 41
Registered: January 2023
|
Member |
|
|
OraFerro wrote on Tue, 31 January 2023 01:58Of course, what I meant is that it avoids the obvious union all by repeating the base table select as in the above examples.
There is no "union all", obvious or otherwise, in unpivoting. The unpivoting solution, whether written with a cross join to a trivial helper table with two rows or using the UNPIVOT operator, does not use UNION ALL, explicitly or implicitly, and does not repeat the base table SELECT. Before you reject an approach, perhaps you could try to understand what it does, instead of just guessing (and guessing wrong) - especially when the approach is pretty basic, used by almost all programmers for this type of problem.
I also don't understand why you insist on writing a solution using MATCH_RECOGNIZE. That feature has many useful applications, but they all have something to do with pattern recognition, one way or another. MATCH_RECOGNIZE is not well suited for unpivoting tasks.
[Updated on: Tue, 31 January 2023 14:13] Report message to a moderator |
|
|
|
| Re: Duplicate rows by generating date based on column value [message #686920 is a reply to message #686919] |
Tue, 31 January 2023 23:02 |
|
OraFerro
Messages: 415
Registered: July 2011
|
Senior Member |
|
|
@mathguy
The union all from the above replies that I am referring to:
SCOTT@orcl_12.1.0.2.0> select ...
2 to_date (...) as generated_date
3 from testc
4 union all
5 select ...
6 to_date (...) as generated_date
7 from testc
8 order by ...;
I also don't understand why you insist on writing a solution using MATCH_RECOGNIZE
I am doing this to explore and understand it more as I explained earlier. But I agree with you it might not be meant for this problem in particular.
|
|
|
|
Goto Forum:
Current Time: Thu Mar 30 19:39:33 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
