Crea una vista pivot in SQL da una tabella SQL

Question

Ho la seguente tabella TEMP

Voglio creare una vista pivot usando SQL, ordinato da CATEGORYASC, da LEVELDESC e SETASC e compilare il file value.

Uscita prevista:

Ho provato il seguente codice ma non riesco a ottenere una soluzione alternativa alla parte aggregata che genera un errore:

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

Inoltre, voglio sapere se può esserci un metodo per aggiungere dinamicamente le colonne e arrivare a questa vista per qualsiasi tabella con le stesse colonne (in modo da evitare l'hardcoding).

So che possiamo farlo in Excel e trasporlo, ma voglio che i dati vengano memorizzati nel db in questo formato.

Answer 1

È possibile creare una funzione memorizzata ( o procedura ) per creare un SQL per Dynamic Pivoting e il set di risultati viene caricato in una variabile di tipo 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;

in cui ho usato due livelli di pivot: il primo è all'interno della query interna che coinvolge PIVOTClause, e il secondo è nella query esterna che ha la logica di aggregazione condizionale. Si noti che l'ordine dei livelli dovrebbe essere nell'ordine discendente ( Z, Y, X) all'interno del risultato atteso conforme alla descrizione.

E poi invoca

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

dalla riga di comando di SQL Developer per ottenere il set di risultati

A proposito, evita di utilizzare parole chiave riservate come sete levelcome nel tuo caso. Dovevo citarli per poterli utilizzare.