Блог

У меня есть следующая таблица TEMP

Я хочу создать сводное представление с использованием SQL, упорядоченное по CATEGORY ASC, по LEVEL DESC и SET ASC и заполнить 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 и перенести его, но я хочу, чтобы данные хранились в БД в этом формате.

Для создания 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;
  

в котором я использовал два уровня поворота: первый находится внутри предложения, включающего внутренний PIVOT запрос, а второй — во внешнем запросе, имеющем логику условного агрегирования. Обратите внимание, что порядок уровней должен быть в порядке убывания ( Z , Y , X ) в пределах ожидаемого результата, соответствующего описанию.

А затем вызвать

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

из командной строки разработчика SQL, чтобы получить набор результатов

Кстати, избегайте использования зарезервированных ключевых слов, таких как set и level как в вашем случае. Мне нужно было указать их, чтобы иметь возможность использовать.