sort with special sort number

I have two tables, sort table and assign table

SortID	Name
0	A
1	B
2	C
3	D

I want assign mission to everyone, start with a special sortID example: start with sortID 2, and change order next week. result as below:

Date	Week	MissionA	MissionB	MissionC	MissionD
2021/1/3	1	C	D	A	B
2021/1/4	1	C	D	A	B
2021/1/5	1	C	D	A	B
2021/1/6	1	C	D	A	B
2021/1/7	1	C	D	A	B
2021/1/8	1	C	D	A	B
2021/1/9	1	C	D	A	B
2021/1/10	2	D	A	B	C
2021/1/11	2	D	A	B	C

Answer

If you do not know the number of rows in the sorts table then you can generate the values as rows (instead of columns):

SELECT d.dt,
       d.week,
       MOD(d.week + s.sortid, s.num_sorts) AS mission,
       s.name
FROM   (
         SELECT DATE '2021-01-02' + LEVEL AS dt,
                CEIL(LEVEL / 7) AS week
         FROM   DUAL
         CONNECT BY LEVEL <= 9
       ) d
       CROSS JOIN (
         SELECT sortid,
                name,
                COUNT(*) OVER() AS num_sorts
         FROM   sorts
       ) s

Which, for the sample data:

CREATE TABLE sorts (SortID, Name) AS
SELECT 0, 'A' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 2, 'C' FROM DUAL UNION ALL
SELECT 3, 'D' FROM DUAL;

Outputs:

DT	WEEK	MISSION	NAME
03-JAN-21	1	1	A
03-JAN-21	1	2	B
03-JAN-21	1	3	C
03-JAN-21	1	0	D
04-JAN-21	1	1	A
04-JAN-21	1	2	B
04-JAN-21	1	3	C
04-JAN-21	1	0	D
05-JAN-21	1	1	A
05-JAN-21	1	2	B
05-JAN-21	1	3	C
05-JAN-21	1	0	D
06-JAN-21	1	1	A
06-JAN-21	1	2	B
06-JAN-21	1	3	C
06-JAN-21	1	0	D
07-JAN-21	1	1	A
07-JAN-21	1	2	B
07-JAN-21	1	3	C
07-JAN-21	1	0	D
08-JAN-21	1	1	A
08-JAN-21	1	2	B
08-JAN-21	1	3	C
08-JAN-21	1	0	D
09-JAN-21	1	1	A
09-JAN-21	1	2	B
09-JAN-21	1	3	C
09-JAN-21	1	0	D
10-JAN-21	2	2	A
10-JAN-21	2	3	B
10-JAN-21	2	0	C
10-JAN-21	2	1	D
11-JAN-21	2	2	A
11-JAN-21	2	3	B
11-JAN-21	2	0	C
11-JAN-21	2	1	D

Note: If you do not know how many columns there will be in the output, it is very difficult to PIVOT the columns to rows as SQL (not just Oracle) does not support dynamic pivoting. Pivoting dynamic data to columns would, typically, be something that is done in the middle tier (PHP, Java, C#, Python, etc.) application.

---

If you know the number of rows in the sorts table then you can use conditional aggregation to pivot the columns to rows:

SELECT d.dt,
       d.week,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 0
         THEN s.name
         END
       ) AS missionA,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 1
         THEN s.name
         END
       ) AS missionB,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 2
         THEN s.name
         END
       ) AS missionC,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 3
         THEN s.name
         END
       ) AS missionD
FROM   (
         SELECT DATE '2021-01-02' + LEVEL AS dt,
                CEIL(LEVEL / 7) AS week
         FROM   DUAL
         CONNECT BY LEVEL <= 9
       ) d
       CROSS JOIN sorts s
GROUP BY dt, week
ORDER BY dt

Which outputs:

DT	WEEK	MISSIONA	MISSIONB	MISSIONC	MISSIOND
03-JAN-21	1	C	D	A	B
04-JAN-21	1	C	D	A	B
05-JAN-21	1	C	D	A	B
06-JAN-21	1	C	D	A	B
07-JAN-21	1	C	D	A	B
08-JAN-21	1	C	D	A	B
09-JAN-21	1	C	D	A	B
10-JAN-21	2	D	A	B	C
11-JAN-21	2	D	A	B	C

db<>fiddle here