Erstellen Sie eine Pivot-Ansicht in SQL aus einer SQL-Tabelle

Question

Ich habe die folgende Tabelle TEMP

Ich möchte eine Pivot-Ansicht mit SQL erstellen, sortiert nach CATEGORYASC, nach LEVELDESC und SETASC und das Feld ausfüllen value.

Erwartete Ausgabe:

Ich habe den folgenden Code ausprobiert, konnte jedoch keine Problemumgehung für den aggregierten Teil finden, der einen Fehler auslöst:

SELECT *
FROM 
    (SELECT 
         SET, LEVEL, CATEGORY, VALUE 
     FROM 
         TEMP 
     ORDER BY 
         CATEGORY ASC, LEVEL DESC, SET ASC) x
PIVOT 
    (value(VALUE) FOR RISK_LEVEL IN ('X','Y','Z') AND CATEGORY IN ('ABC', 'DEF', 'GHI', 'JKL')) p

Außerdem möchte ich wissen, ob es eine Methode gibt, um die Spalten dynamisch hinzuzufügen und zu dieser Ansicht für eine Tabelle mit denselben Spalten zu gelangen (damit eine Hardcodierung vermieden werden kann).

Ich weiß, dass wir dies in Excel tun und transponieren können, aber ich möchte, dass die Daten in diesem Format in der Datenbank gespeichert werden.

Answer 1

Möglicherweise wird eine gespeicherte Funktion ( oder Prozedur ) erstellt, um eine SQL für dynamisches Pivoting zu erstellen, und die Ergebnismenge wird in eine Variable vom Typ geladen SYS_REFCURSOR:

CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols_1    VARCHAR2(32767);
  v_cols_2    VARCHAR2(32767);  
BEGIN
  SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
          WITHIN GROUP ( ORDER BY "level" DESC )
    INTO v_cols_1
    FROM (
          SELECT DISTINCT "level"
            FROM temp
          );

  SELECT LISTAGG( 'MAX(CASE WHEN category = '''||category||''' THEN "'||"level"||'" END) AS "'||"level"||'_'||category||'"' , ',' )
          WITHIN GROUP ( ORDER BY category, "level" DESC )
    INTO v_cols_2
    FROM (
          SELECT DISTINCT "level", category
            FROM temp
          );

  v_sql :=
  'SELECT "set", '|| v_cols_2 ||'
     FROM
     (
      SELECT *
        FROM temp
       PIVOT
       (
        MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
       )
      )
      GROUP BY "set"
      ORDER BY "set"'; 

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

Ich habe zwei Pivoting-Ebenen verwendet: Die erste befindet sich in der inneren Abfrage mit PIVOTKlausel und die zweite in der äußeren Abfrage mit der bedingten Aggregationslogik. Beachten Sie, dass die Reihenfolge der Ebenen in der absteigenden Reihenfolge sein soll ( Z, Y, X) innerhalb des erwarteten Ergebnisses in Bezug auf die Beschreibung entspricht.

Und dann aufrufen

VAR rc REFCURSOR
EXEC :rc := Get_Categories_RS;
PRINT rc

über die Befehlszeile von SQL Developer, um die Ergebnismenge abzurufen

Vermeiden Sie übrigens die Verwendung reservierter Schlüsselwörter wie setund levelwie in Ihrem Fall. Ich musste sie zitieren, um sie verwenden zu können.