Динамический сводной запрос с использованием Sql Developer Oracle

Начальная таблица учеников

NAME     School    Class
 John     Hs      English
 Steve    Hs      Maths
 Matthew  Hs      Science
 Jim      Hs      History

Требуемый вывод: мне нужен запрос для автоматического получения данных столбца имени из исходной таблицы и изменения его на заголовки столбцов в выводе, и, поскольку имена будут постоянно меняться, я не могу жестко закодировать имена, используя простой сводной запрос. Я новичок в сводных запросах, поэтому я хотел спросить, может ли кто-нибудь помочь мне. Благодарю вас.

School  John     Steve  Matthew  Jim
Hs      English  Maths  Science  History

Вот что я пробовал: * Примечание (я пытаюсь использовать этот запрос в Oracle Sql Developer для достижения выходного формата)

declare 
        sqlqry clob;
        cols clob;
  begin 
  select listagg('''' || NAME || ''' as "' || NAME || '"', ',') within group   (order by NAME)
  into cols
  from (select distinct NAME from Students);
  sqlqry := 
  '
   select * from(select NAME,SCHOOL,CLASS from Students)
   pivot(MAX(CLASS) FOR NAME IN (' || cols || ')
   )';
   execute immediate sqlqry;
   end;

oracle pivot plsql oracle-sqldeveloper dynamic-pivot
person Unknown    schedule 01.09.2020    source источник
comment
Часть pl/sql кажется прекрасной. любая ошибка?   -  person Koushik Roy    schedule 02.09.2020
comment
Да, когда я запускаю это в разработчике sql, он выдает неизвестную ошибку в строке 1 команды.   -  person Unknown    schedule 04.09.2020
comment
Не думаю, что так надо писать. PL SQL имеет некоторые проблемы. это работает select * from(select NAME,SCHOOL,CLASS from Students) pivot(MAX(CLASS) FOR NAME IN ( select listagg('''' || NAME || ''' as "' || NAME || '"', ',') within group (order by NAME) as col from (select distinct NAME from Students)) )   -  person Koushik Roy    schedule 21.09.2020
comment
Это дает мне ошибку отсутствующего выражения   -  person Unknown    schedule 23.09.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вы на правильном пути, но этот случай терпит неудачу, когда более одного класса определено по крайней мере для одного ученика. Аналитическая функция ROW_NUMBER() решает проблему. Итак, создайте хранимую функцию, включающую SYS_REFCURSOR :

CREATE OR REPLACE FUNCTION get_student_rs RETURN SYS_REFCURSOR IS
  recordset SYS_REFCURSOR;
  sqlqry    VARCHAR2(32767);
  cols      VARCHAR2(32767);
BEGIN
  SELECT LISTAGG(''''||name||''' AS "'||name||'"' ,',') WITHIN GROUP (ORDER BY 0)
    INTO cols
    FROM ( SELECT DISTINCT name FROM Students );  

  sqlqry :=
  'SELECT *
     FROM 
     (
      SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY 0) AS rn,
             s.*             
        FROM Students s     
      )
    PIVOT (
           MAX(class) FOR name IN ('||cols||')
           )
    ORDER BY rn';

  OPEN recordset FOR sqlqry;
  RETURN recordset;
END;
/

Предположим, что вставлена ​​еще одна запись ;

Name     School    Class
----     ------    -------
Jim      Hs        History

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

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

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

RN  SCHOOL  Jim      John     Matthew  Steve
--  ------  -------  -------  -------  ------
1   Hs      Maths    English  Science  Maths
2   Hs      History
person Barbaros Özhan    schedule 01.10.2020