Créer une vue Pivot dans SQL à partir d'une table SQL

Question

J'ai le tableau suivant TEMP

Je veux créer une vue pivot à l'aide de SQL, triée par CATEGORYASC, par LEVELDESC et SETASC et remplir le fichier value.

Production attendue:

J'ai essayé le code suivant mais je n'ai pas réussi à obtenir une solution de contournement de la partie agrégée qui lève une erreur:

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

De plus, je veux savoir s'il peut y avoir une méthode pour ajouter dynamiquement les colonnes et arriver à cette vue pour n'importe quelle table ayant les mêmes colonnes (afin que le codage en dur puisse être évité).

Je sais que nous pouvons le faire dans Excel et le transposer, mais je veux que les données soient stockées dans la base de données dans ce format.

Answer 1

Une fonction ( ou procédure ) stockée peut être créée afin de créer un SQL pour le pivotement dynamique, et le jeu de résultats est chargé dans une variable de 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;

dans lequel j'ai utilisé deux niveaux de pivotement: le premier est dans la requête interne impliquant PIVOTClause, et le second est dans la requête externe ayant la logique d'agrégation conditionnelle. Notez que l'ordre des niveaux devrait être dans l'ordre décroissant ( Z, Y, X) dans le résultat attendu comme étant conforme à la description.

Et puis invoquez

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

à partir de la ligne de commande de SQL Developer afin d'obtenir le jeu de résultats

Btw, évitez d'utiliser des mots clés réservés tels que setet levelcomme dans votre cas. J'avais besoin de les citer pour pouvoir les utiliser.