oracle - Create Pivot view in SQL from a SQL table

Question

I have the following table TEMP

I want to create a pivot view using SQL, Ordered by CATEGORY ASC ,by LEVEL DESC and SET ASC and fill in the value .

Expected output:

I have tried the following code but unable to get a workaround the aggregate part which is throwing an error:

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

Furthermore I want to know if there can be any method for dynamically adding the columns and arriving at this view for any table having the same columns (so that hardcoding can be avoided).

I know we can do this in Excel and transpose it, but I want the data to be stored in the db in this format.

Answer 1

A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type 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 which I used two levels of pivoting : the first is within the inner query involving PIVOT Clause, and the second is in the outer query having the conditional aggregation logic. Notice that the order of levels should be in the descending order( Z, Y, X ) within the expected result as conforming to the description.

And then invoke

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

from SQL Developer's Command Line in order to get the result set

Btw, avoid using reserved keywords such as set and level as in your case. I needed to quote them in order to be able to use.