SQLテーブルからSQLでピボットビューを作成する

Question

私は次の表を持っています TEMP

SQLを使用して、CATEGORYASC、LEVELDESC、SETASCの順にピボットビューを作成し、を入力しますvalue。

期待される出力：

次のコードを試しましたが、エラーをスローしている集約部分の回避策を取得できませんでした。

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

さらに、列を動的に追加し、同じ列を持つテーブルに対してこのビューに到達する方法があるかどうかを知りたいです（ハードコーディングを回避できるようにするため）。

これをExcelで実行して転置できることはわかっていますが、データをこの形式でデータベースに保存したいと思います。

Answer 1

動的ピボット用のSQLを作成するために、ストアド関数（またはプロシージャ）が作成され、結果セットが次のタイプの変数にロードされます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;

ここでは、2つのレベルのピボットを使用しました。1つPIVOT目はClauseを含む内部クエリ内にあり、2つ目は条件付き集計ロジックを持つ外部クエリ内にあります。レベルの順序は、降順（であるべきである旨の通知Z、Y、X説明に準拠するよう期待される結果以内）。

そして、呼び出す

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

結果セットを取得するためにSQLDeveloperのコマンドラインから

ところで、などの予約キーワードの使用を避けるsetとlevel、あなたの場合のように。使用できるようにするには、それらを引用する必要がありました。