Buat tampilan Pivot di SQL dari tabel SQL

Question

Saya memiliki tabel berikut TEMP

Saya ingin membuat tampilan pivot menggunakan SQL, Dipesan oleh CATEGORYASC, oleh LEVELDESC dan SETASC dan mengisi file value.

Output yang diharapkan:

Saya telah mencoba kode berikut tetapi tidak bisa mendapatkan solusi untuk bagian agregat yang menimbulkan kesalahan:

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

Selain itu, saya ingin tahu apakah ada metode untuk menambahkan kolom secara dinamis dan sampai pada tampilan ini untuk tabel mana pun yang memiliki kolom yang sama (sehingga hardcoding dapat dihindari).

Saya tahu kita bisa melakukan ini di Excel dan mengubahnya, tapi saya ingin datanya disimpan dalam db dalam format ini.

Answer 1

Fungsi tersimpan ( atau prosedur ) mungkin dibuat untuk membuat SQL untuk Dynamic Pivoting, dan kumpulan hasil dimuat ke dalam tipe variabel 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;

di mana saya menggunakan dua tingkat pivot: yang pertama ada di dalam kueri dalam yang melibatkan PIVOTKlausa, dan yang kedua ada di kueri luar yang memiliki logika agregasi bersyarat. Perhatikan bahwa urutan tingkat harus dalam urutan menurun ( Z, Y, X) dalam hasil yang diharapkan sebagai sesuai dengan deskripsi.

Dan kemudian panggil

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

dari Baris Perintah Pengembang SQL untuk mendapatkan set hasil

Btw, hindari menggunakan kata kunci yang dipesan seperti setdan levelseperti dalam kasus Anda. Saya perlu mengutipnya agar bisa digunakan.