Umsortierung von Werten in einem Datensatz

Wieder mal ein aussageschwacher Titel - aber in diesem Fall glaube ich, dass die Fragestellung tatsächlich schwer auf den Punkt zu bringen ist. Worum es geht, ist Folgendes: im OTN-Forum SQL and PL/SQL wurde dieser Tage die Frage gestellt, wie man die Daten der folgenden Tabelle so umordnen kann, dass die nach der ersten Spalte (col_1_rank) sortierte Kombination der ersten beiden Spalten (col_1_rank, col_1_value) mit der nach der dritten Spalte (col_2_rank) sortierten Kombination der Spalten drei und view (col_2_rank, col_2_value) verknüpft wird.

col_1_rank	col_1_value	col_2_rank	col_2_value
1	AAA	3	HHH
2	BBB	2	GGG
3	CCC	5	JJJ
4	DDD	1	FFF
5	EEE	4	III

Als Ergebnis sollte sich also die folgende Tabelle ergeben:

col_1_rank	col_1_value	col_2_rank	col_2_value
1	AAA	1	FFF
2	BBB	2	GGG
3	CCC	3	HHH
4	DDD	4	III
5	EEE	5	JJJ

Dabei galt die zusätzliche Einschränkung, dass zur Umsetzung kein Join verwendet werden durfte, weil nur ein Scan der Tabelle durchgeführt werden sollte.

Nun könnte man sicher einwenden, dass das eine recht bizarre Anforderung ist, und dass der einfache table scan mit komplexeren analytischen Funktionen unter Umständen teurer sein könnte, als ein Join der Daten der beiden logischen Blöcke in der Tabelle - aber als merkwürdige SQL-Fragestellung fand ich die Anforderung interessant genug, um eine Lösung zum Thema beizusteuern:

drop table t;
 
create table t (
    col_1_rank number
  , col_1_value varchar2(20)
  , col_2_rank number
  , col_2_value varchar2(20)
);
 
insert into t values (1, 'AAA', 3, 'HHH');
insert into t values (2, 'BBB', 2, 'GGG');
insert into t values (3, 'CCC', 5, 'JJJ');
insert into t values (4, 'DDD', 1, 'FFF');
insert into t values (5, 'EEE', 4, 'III');
 
with
basedata as (
select col_1_rank
     , col_1_value
     , ',' || listagg(col_2_value, ',') within group (order by col_2_rank) over() || ',' col_2_value
  from t
)
select col_1_rank
     , col_1_value
     , substr( col_2_value
             , instr(col_2_value, ',', 1, col_1_rank) + 1
             , (instr(col_2_value, ',', 1, col_1_rank + 1) - 1) - (instr(col_2_value, ',', 1, col_1_rank))
              ) col_2_value
  from basedata
order by col_1_rank;

COL_1_RANK COL_1_VALUE          COL_2_VALUE
---------- -------------------- ------------------------------
         1 AAA                  FFF
         2 BBB                  GGG
         3 CCC                  HHH
         4 DDD                  III
         5 EEE                  JJJ

Mir schien das als obskure Lösung zu einer obskuren Fragestellung ganz angemessen, aber kurz nach meiner Antwort kam Frank Kulashs Lösungsvorschlag:

SELECT   *
FROM      t
UNPIVOT   (               (mutual_rank, val)
          FOR  label IN ( (col_1_rank,  col_1_value)  AS 1
                        , (col_2_rank,  col_2_value)  AS 2
                        )
          )
PIVOT     (    MIN (val)
          FOR  label  IN ( 1  AS col_1_value
                         , 2  AS col_2_vlaue
                         )
          )
ORDER BY  mutual_rank
;

MUTUAL_RANK COL_1_VALUE          COL_2_VLAUE
----------- -------------------- --------------------
          1 AAA                  FFF
          2 BBB                  GGG
          3 CCC                  HHH
          4 DDD                  III
          5 EEE                  JJJ

-- wobei der UNPIVOT-Abschnitt die eingehenden Datensätze
-- in ihre beiden logischen Abschnitte teilt
-- und die PIVOT-Klausel für die neue Verknüpfung zuständig ist
SELECT   *
FROM      t
UNPIVOT   (               (mutual_rank, val)
          FOR  label IN ( (col_1_rank,  col_1_value)  AS 1
                        , (col_2_rank,  col_2_value)  AS 2
                        )
          );

     LABEL MUTUAL_RANK VAL
---------- ----------- --------------------
         1           1 AAA
         2           3 HHH
         1           2 BBB
         2           2 GGG
         1           3 CCC
         2           5 JJJ
         1           4 DDD
         2           1 FFF
         1           5 EEE
         2           4 III

Da die LISTAGG-Variante spätestens am 4000 Byte-Limit (= maximale Größe für VARCHAR2) des Ergebnisses an ihre Grenzen stößt, ist die PIVOT/UNPIVOT-Lösung deutlich nützlicher. Ohnehin ist es mein Prinzip im OTN-Forum nichts in Threads zu schreiben, an denen der Herr Kulash beteiligt ist, weil der normalerweise ohnehin schon die passende Antwort gegeben hat...