Home » SQL & PL/SQL » SQL & PL/SQL » Sorting Versions into rows (Oracle Database 19c Enterprise Edition)
| Sorting Versions into rows [message #690259] |
Wed, 29 January 2025 05:10  |
 |
quirks
Messages: 84
Registered: October 2014
|
Member |
|
|
Hello,
I've got a table in which versions and the start and end date of their use are listed. It kind of looks like this:
WITH versions AS (SELECT 'A' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('03.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'B' AS version
, TO_DATE('02.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('04.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'C' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('06.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'D' AS version
, TO_DATE('05.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'E' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'F' AS version
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual)
SELECT * from versions;
SELECT TO_DATE('01.01.2025', 'DD.MM.YYYY') as day, 'E' as "1", 'A' as "2", 'C' as "3", NULL as "4" from dual
union ALL
SELECT TO_DATE('02.01.2025', 'DD.MM.YYYY') as day, 'B' as "1", 'A' as "2", 'C' as "3", NULL as "4" from dual
union ALL
SELECT TO_DATE('03.01.2025', 'DD.MM.YYYY') as day, 'B' as "1", 'A' as "2", 'C' as "3", NULL as "4" from dual
union ALL
SELECT TO_DATE('04.01.2025', 'DD.MM.YYYY') as day, 'B' as "1", NULL as "2", 'C' as "3", NULL as "4" from dual
union ALL
SELECT TO_DATE('05.01.2025', 'DD.MM.YYYY') as day, 'D' as "1", NULL as "2", 'C' as "3", NULL as "4" from dual
union ALL
SELECT TO_DATE('06.01.2025', 'DD.MM.YYYY') as day, 'D' as "1", NULL as "2", 'C' as "3", NULL as "4" from dual
union ALL
SELECT TO_DATE('07.01.2025', 'DD.MM.YYYY') as day, 'D' as "1", 'F' as "2", NULL as "3", NULL as "4" from dual
;
WITH versions AS (SELECT 'A' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('03.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'B' AS version
, TO_DATE('02.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('04.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'C' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('06.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'D' AS version
, TO_DATE('05.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'E' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'F' AS version
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual)
, date_range AS (SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM versions)
, dates AS (SELECT start_date - 1 + level day FROM date_range CONNECT BY level <= end_date - start_date + 1)
, rank_versions AS (SELECT dates.day
, version
, RANK() OVER (PARTITION BY day ORDER BY start_date, end_date) AS ver_rank
FROM versions
JOIN dates
ON dates.day BETWEEN versions.start_date AND versions.end_date)
SELECT day, "1", "2", "3", "4"
FROM rank_versions PIVOT ( MAX(version) FOR ver_rank IN (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ))
order by day; |
|
|
|
| Re: Sorting Versions into rows [message #690260 is a reply to message #690259] |
Thu, 30 January 2025 10:59  |
|
quirks
Messages: 84
Registered: October 2014
|
Member |
|
|
Here is my next approach, which gives the correct results, but somehow I still think that there must be a better solution. It feels wrong to tackle this problem in that way.
WITH versions AS (SELECT 'A' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('03.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'B' AS version
, TO_DATE('02.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('04.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'C' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('06.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'D' AS version
, TO_DATE('05.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'E' AS version
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('01.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
UNION ALL
SELECT 'F' AS version
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS start_date
, TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
FROM dual
-- UNION ALL
-- SELECT 'X' AS version
-- , TO_DATE('05.01.2025', 'DD.MM.YYYY') AS start_date
-- , TO_DATE('05.01.2025', 'DD.MM.YYYY') AS end_date
-- FROM dual
-- UNION ALL
-- SELECT 'Y' AS version
-- , TO_DATE('02.01.2025', 'DD.MM.YYYY') AS start_date
-- , TO_DATE('05.01.2025', 'DD.MM.YYYY') AS end_date
-- FROM dual
)
, date_range AS (SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM versions)
, dates AS (SELECT start_date - 1 + level day FROM date_range CONNECT BY level <= end_date - start_date + 1)
, rank_versions AS (SELECT dates.day
, version
, RANK() OVER (PARTITION BY day ORDER BY start_date, end_date) AS ver_rank
FROM versions
JOIN dates
ON dates.day BETWEEN versions.start_date AND versions.end_date)
, pivot_versions AS (SELECT day, "1", "2", "3", "4"
FROM rank_versions PIVOT ( MAX(version) FOR ver_rank IN (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ))
ORDER BY day)
SELECT
-- *
day, list1, list2, list3, list4
FROM pivot_versions
MODEL IGNORE NAV
DIMENSION BY (day)
MEASURES (
"1"
, "2"
, "3"
, "4"
, CAST('' AS VARCHAR(400)) AS col4
, CAST('' AS VARCHAR(400)) AS list1
, CAST('' AS VARCHAR(400)) AS list2
, CAST('' AS VARCHAR(400)) AS list3
, CAST('' AS VARCHAR(400)) AS list4
, CAST('' AS VARCHAR(400)) AS col5)
RULES (
col4[any] = ("1"[CV()] || "2"[CV()] || "3"[CV()] || "4"[CV()]), list1[any] =
CASE
WHEN col4[CV() - 1] IS NULL THEN "1"[CV()]
WHEN INSTR(col4[CV()], list1[CV() - 1]) > 0 THEN list1[CV() - 1]
ELSE
CASE
WHEN INSTR(col4[CV() - 1], "2"[CV()]) = 0 THEN "2"[CV()]
WHEN INSTR(col4[CV() - 1], "3"[CV()]) = 0 THEN "3"[CV()]
WHEN INSTR(col4[CV() - 1], "4"[CV()]) = 0 THEN "4"[CV()]
END
END
, list2[any] =
CASE
WHEN col4[CV() - 1] IS NULL THEN "2"[CV()]
WHEN INSTR(col4[CV()], list2[CV() - 1]) > 0 THEN list2[CV() - 1]
WHEN INSTR(col4[CV() - 1], "2"[CV()]) = 0 AND INSTR(list1[CV()], "2"[CV()]) = 0
THEN "2"[CV()]
WHEN INSTR(col4[CV() - 1], "3"[CV()]) = 0 AND INSTR(list1[CV()], "3"[CV()]) = 0
THEN "3"[CV()]
WHEN INSTR(col4[CV() - 1], "4"[CV()]) = 0 AND INSTR(list1[CV()], "4"[CV()]) = 0
THEN "4"[CV()]
END
, list3[any] =
CASE
WHEN col4[CV() - 1] IS NULL
THEN "3"[CV()]
WHEN INSTR(col4[CV()], list3[CV() - 1]) > 0
THEN list3[CV() - 1]
WHEN INSTR(col4[CV() - 1], "2"[CV()]) = 0 AND INSTR(list1[CV()] || list2[CV()], "2"[CV()]) = 0
THEN "2"[CV()]
WHEN INSTR(col4[CV() - 1], "3"[CV()]) = 0 AND INSTR(list1[CV()] || list2[CV()], "3"[CV()]) = 0
THEN "3"[CV()]
WHEN INSTR(col4[CV() - 1], "4"[CV()]) = 0 AND INSTR(list1[CV()] || list2[CV()], "4"[CV()]) = 0
THEN "4"[CV()]
END
, list4[any] =
CASE
WHEN col4[CV() - 1] IS NULL
THEN "4"[CV()]
WHEN INSTR(col4[CV()], list4[CV() - 1]) > 0
THEN list4[CV() - 1]
WHEN INSTR(col4[CV() - 1], "2"[CV()]) = 0 AND INSTR(list1[CV()] || list2[CV()] || list3[CV()], "2"[CV()]) = 0
THEN "2"[CV()]
WHEN INSTR(col4[CV() - 1], "3"[CV()]) = 0 AND INSTR(list1[CV()] || list2[CV()] || list3[CV()], "3"[CV()]) = 0
THEN "3"[CV()]
WHEN INSTR(col4[CV() - 1], "4"[CV()]) = 0 AND INSTR(list1[CV()] || list2[CV()] || list3[CV()], "4"[CV()]) = 0
THEN "4"[CV()]
END
)
ORDER BY 1; |
|
|
|
| Re: Sorting Versions into rows [message #690261 is a reply to message #690260] |
Thu, 30 January 2025 13:15 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is a PL/SQL option for your consideration.
-- create a table from your rank_versions:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_data AS
2 WITH versions AS (SELECT 'A' AS version
3 , TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
4 , TO_DATE('03.01.2025', 'DD.MM.YYYY') AS end_date
5 FROM dual
6 UNION ALL
7 SELECT 'B' AS version
8 , TO_DATE('02.01.2025', 'DD.MM.YYYY') AS start_date
9 , TO_DATE('04.01.2025', 'DD.MM.YYYY') AS end_date
10 FROM dual
11 UNION ALL
12 SELECT 'C' AS version
13 , TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
14 , TO_DATE('06.01.2025', 'DD.MM.YYYY') AS end_date
15 FROM dual
16 UNION ALL
17 SELECT 'D' AS version
18 , TO_DATE('05.01.2025', 'DD.MM.YYYY') AS start_date
19 , TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
20 FROM dual
21 UNION ALL
22 SELECT 'E' AS version
23 , TO_DATE('01.01.2025', 'DD.MM.YYYY') AS start_date
24 , TO_DATE('01.01.2025', 'DD.MM.YYYY') AS end_date
25 FROM dual
26 UNION ALL
27 SELECT 'F' AS version
28 , TO_DATE('07.01.2025', 'DD.MM.YYYY') AS start_date
29 , TO_DATE('07.01.2025', 'DD.MM.YYYY') AS end_date
30 FROM dual
31 ORDER BY start_date, end_date)
32 , date_range AS (SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM versions)
33 , dates AS (SELECT start_date - 1 + level day FROM date_range CONNECT BY level <= end_date - start_date + 1)
34 , rank_versions AS (SELECT dates.day
35 , version
36 , RANK() OVER (PARTITION BY day ORDER BY start_date, end_date) AS ver_rank
37 FROM versions
38 JOIN dates
39 ON dates.day BETWEEN versions.start_date AND versions.end_date)
40 SELECT *
41 FROM rank_versions
42 /
Table created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_data ORDER BY day, ver_rank
2 /
DAY V VER_RANK
--------------- - ----------
Wed 01-Jan-2025 E 1
Wed 01-Jan-2025 A 2
Wed 01-Jan-2025 C 3
Thu 02-Jan-2025 A 1
Thu 02-Jan-2025 C 2
Thu 02-Jan-2025 B 3
Fri 03-Jan-2025 A 1
Fri 03-Jan-2025 C 2
Fri 03-Jan-2025 B 3
Sat 04-Jan-2025 C 1
Sat 04-Jan-2025 B 2
Sun 05-Jan-2025 C 1
Sun 05-Jan-2025 D 2
Mon 06-Jan-2025 C 1
Mon 06-Jan-2025 D 2
Tue 07-Jan-2025 D 1
Tue 07-Jan-2025 F 2
17 rows selected.
-- create a table to hold the results:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE results
2 (day DATE,
3 list1 VARCHAR2(5),
4 list2 VARCHAR2(5),
5 list3 VARCHAR2(5),
6 list4 VARCHAR2(5))
7 /
Table created.
-- use some PL/SQL to select from the test_data table and insert into the results table:
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_date DATE;
3 v_max_date DATE;
4 v_list1 VARCHAR2(5);
5 v_list2 VARCHAR2(5);
6 v_list3 VARCHAR2(5);
7 v_list4 VARCHAR2(5);
8 v_list1n VARCHAR2(5);
9 v_list2n VARCHAR2(5);
10 v_list3n VARCHAR2(5);
11 v_list4n VARCHAR2(5);
12 BEGIN
13 SELECT MIN(day) INTO v_date FROM test_data;
14 SELECT MAX(day) INTO v_max_date FROM test_data;
15 WHILE v_date <= v_max_date
16 LOOP
17 FOR r IN
18 (SELECT *
19 FROM test_data
20 WHERE day = v_date
21 ORDER BY ver_rank)
22 LOOP
23 IF v_list1 = r.version THEN v_list1n := r.version;
24 ELSIF v_list2 = r.version THEN v_list2n := r.version;
25 ELSIF v_list3 = r.version THEN v_list3n := r.version;
26 ELSIF v_list4 = r.version THEN v_list4n := r.version;
27 ELSIF v_list1n IS NULL THEN v_list1n := r.version;
28 ELSIF v_list2n IS NULL THEN v_list2n := r.version;
29 ELSIF v_list3n IS NULL THEN v_list3n := r.version;
30 ELSIF v_list4n IS NULL THEN v_list4n := r.version;
31 END IF;
32 END LOOP;
33 INSERT INTO results VALUES (v_date, v_list1n, v_list2n, v_list3n, v_list4n);
34 v_list1 := v_list1n;
35 v_list2 := v_list2n;
36 v_list3 := v_list3n;
37 v_list4 := v_list4n;
38 v_list1n := null;
39 v_list2n := null;
40 v_list3n := null;
41 v_list4n := null;
42 v_date := v_date + 1;
43 END LOOP;
44 END;
45 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM results ORDER BY day
2 /
DAY LIST1 LIST2 LIST3 LIST4
--------------- ----- ----- ----- -----
Wed 01-Jan-2025 E A C
Thu 02-Jan-2025 B A C
Fri 03-Jan-2025 B A C
Sat 04-Jan-2025 B C
Sun 05-Jan-2025 D C
Mon 06-Jan-2025 D C
Tue 07-Jan-2025 D F
7 rows selected.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 16:52:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
