pivot允许您在行中显示列数据。您想要将两列转换成行，这有点棘手。我为每个子查询添加了一个注解来解释我所做的事情。理解它的最好方法是再次拆开它，然后检查发生了什么。

WITH /* sample data */
mydata (thedate, shop, rank_1, rank_2) AS
(
  SELECT DATE'2020-01-01', 'ZZZ', 1, 3 FROM DUAL
  UNION
  SELECT DATE'2020-01-01', 'YYY', 2, 4 FROM DUAL
  UNION  
  SELECT DATE'2020-01-01', 'DDD', 3, 5 FROM DUAL
  UNION  
  SELECT DATE'2020-01-01', 'UUU', 4, 1 FROM DUAL
  UNION  
  SELECT DATE'2020-01-01', 'LLLLL', 5, 2 FROM DUAL
  UNION  
  SELECT DATE'2020-01-02', 'AAA', 5, 2 FROM DUAL
), 
/* extract only first column */
my_data_rank1 (thedate, shop, rank_1)
AS
(
SELECT thedate,
       shop,
       rank_1
  FROM mydata
),
/* extract only 2nd column */
my_data_rank2 (thedate, shop, rank_2)
AS
(
SELECT thedate,
       shop,
       rank_2
  FROM mydata
),
/* pivot on 1st column */
rank1_pivot_data AS
(
SELECT * FROM my_data_rank1  
PIVOT (MAX(shop) for rank_1 IN ('1' rank_1_1, '2' rank_1_2, '3' rank_1_3 , '4' rank_1_4 , '5' rank_1_5 ))
),
/* pivot on 2nd column */
 rank2_pivot_data AS
(
SELECT * FROM my_data_rank2  
PIVOT (MAX(shop) for rank_2 IN ('1' rank_2_1, '2' rank_2_2, '3' rank_2_3 , '4' rank_2_4 , '5' rank_2_5 ))
),
/* combine both rows. this will give you 2 rows: one for rank_1 and one for rank_2 */
combined_data (thedate, rank_1_1,rank_1_2,rank_1_3,rank_1_4,rank_1_5,rank_2_1,rank_2_2,rank_2_3,rank_2_4,rank_2_5) AS
(
 SELECT thedate, rank_1_1,rank_1_2,rank_1_3,rank_1_4,rank_1_5,null,null,null,null,null
   FROM rank1_pivot_data
 UNION ALL
 SELECT thedate, null,null,null,null,null,rank_2_1,rank_2_2,rank_2_3,rank_2_4,rank_2_5
   FROM rank2_pivot_data
),
/* merge the rows into 1 */
combined_data_merged  (thedate, rank_1_1,rank_1_2,rank_1_3,rank_1_4,rank_1_5,rank_2_1,rank_2_2,rank_2_3,rank_2_4,rank_2_5) 
AS 
(
 SELECT thedate, MAX(rank_1_1),MAX(rank_1_2),MAX(rank_1_3),MAX(rank_1_4),MAX(rank_1_5),MAX(rank_2_1),MAX(rank_2_2),MAX(rank_2_3),MAX(rank_2_4),MAX(rank_2_5)
   FROM combined_data
   GROUP BY thedate
)
SELECT * FROM combined_data_merged;

THEDATE     RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_
----------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
01-JAN-2020 ZZZ   YYY   DDD   UUU   LLLLL UUU   LLLLL ZZZ   YYY   DDD  
02-JAN-2020                         AAA         AAA