Tạo dạng xem Pivot trong SQL từ bảng SQL

Question

Tôi có bảng sau TEMP

Tôi muốn tạo một dạng xem xoay vòng bằng SQL, Được sắp xếp bởi CATEGORYASC, bởi LEVELDESC và SETASC và điền vào value.

Sản lượng mong đợi:

Tôi đã thử mã sau nhưng không thể giải quyết được phần tổng hợp đang gặp lỗi:

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

Hơn nữa, tôi muốn biết liệu có thể có bất kỳ phương pháp nào để thêm động các cột và đến chế độ xem này cho bất kỳ bảng nào có cùng các cột hay không (để có thể tránh được mã cứng).

Tôi biết chúng ta có thể thực hiện việc này trong Excel và chuyển đổi nó, nhưng tôi muốn dữ liệu được lưu trữ trong db ở định dạng này.

Answer 1

Một hàm ( hoặc thủ tục ) được lưu trữ có thể được tạo để tạo SQL cho Dynamic Pivoting và tập kết quả được tải vào một biến kiểu 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;

trong đó tôi đã sử dụng hai mức xoay vòng: thứ nhất là trong truy vấn bên trong liên quan đến PIVOTMệnh đề và thứ hai là trong truy vấn bên ngoài có logic tổng hợp có điều kiện. Chú ý rằng thứ tự các cấp phải ở trong thứ tự giảm dần ( Z, Y, X) trong kết quả dự kiến như phù hợp với mô tả.

Và sau đó gọi

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

từ Dòng lệnh của Nhà phát triển SQL để có được bộ kết quả

Btw, tránh sử dụng các từ khóa dành riêng như setvà leveltrong trường hợp của bạn. Tôi cần phải trích dẫn chúng để có thể sử dụng.