pivot in oracle дает ошибку для sql-запроса после в [duplicate]

ИСПОЛЬЗОВАТЬ ДИНАМИЧЕСКИЙ ЗАПРОС

Тестовый код ниже

--  DDL for Table TMP_TEST
--------------------------------------------------------

  CREATE TABLE "TMP_TEST" 
   (    "NAME" VARCHAR2(20), 
    "APP" VARCHAR2(20)
   );
/
SET DEFINE OFF;
Insert into TMP_TEST (NAME,APP) values ('suhaib','2');
Insert into TMP_TEST (NAME,APP) values ('suhaib','1');
Insert into TMP_TEST (NAME,APP) values ('shahzad','3');
Insert into TMP_TEST (NAME,APP) values ('shahzad','2');
Insert into TMP_TEST (NAME,APP) values ('shahzad','5');
Insert into TMP_TEST (NAME,APP) values ('tariq','1');
Insert into TMP_TEST (NAME,APP) values ('tariq','2');
Insert into TMP_TEST (NAME,APP) values ('tariq','6');
Insert into TMP_TEST (NAME,APP) values ('tariq','4');
/
  CREATE TABLE "TMP_TESTAPP" 
   (    "APP" VARCHAR2(20)
   );

SET DEFINE OFF;
Insert into TMP_TESTAPP (APP) values ('1');
Insert into TMP_TESTAPP (APP) values ('2');
Insert into TMP_TESTAPP (APP) values ('3');
Insert into TMP_TESTAPP (APP) values ('4');
Insert into TMP_TESTAPP (APP) values ('5');
Insert into TMP_TESTAPP (APP) values ('6');
/
create or replace PROCEDURE temp_test(
  pcursor out sys_refcursor,
    PRESULT                   OUT VARCHAR2
    )
AS
V_VALUES VARCHAR2(4000);
V_QUERY VARCHAR2(4000);
BEGIN
 PRESULT := 'Nothing';

-- concating activities name using comma, replace "'" with "''" because we will use it in dynamic query so "'" can effect query.
  SELECT DISTINCT 
         LISTAGG('''' || REPLACE(APP,'''','''''') || '''',',')
         WITHIN GROUP (ORDER BY APP) AS temp_in_statement 
    INTO V_VALUES
    FROM (SELECT DISTINCT APP 
            FROM TMP_TESTAPP);

-- designing dynamic query  

  V_QUERY := 'select * 
                from (  select NAME,APP 
                          from TMP_TEST   )   
               pivot (count(*) for APP in 
                     (' ||V_VALUES|| '))  
           order  by NAME' ;

    OPEN PCURSOR
     FOR V_QUERY;


 PRESULT := 'Success';

Exception
WHEN OTHERS THEN
 PRESULT := SQLcode || ' - ' || SQLERRM;
END temp_test;

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

Здесь мы должны использовать динамический sql, изначально извлекаем значения столбца в переменную и передачу переменной внутри динамического sql.

ПРИМЕР

. У нас есть таблица, как показано ниже.

Если нам нужно показать значения в столбце YR как имена столбцов и значения в этих столбцах из QTY, тогда мы можем использовать приведенный ниже код.

declare
  sqlqry clob;
  cols clob;
begin
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);


  sqlqry :=
  '      
  select * from
  (
      select *
      from EMPLOYEE
  )
  pivot
  (
    MIN(QTY) for YR in (' || cols  || ')
  )';

  execute immediate sqlqry;
end;
/

RESULT

При необходимости вы также можете создать временную таблицу и выполнить запрос выбора в этой временной таблице, чтобы увидеть результаты. Его простое, просто добавьте CREATE TABLE TABLENAME AS в приведенный выше код.

sqlqry :=
'    
  CREATE TABLE TABLENAME AS
  select * from

Для более поздних читателей здесь есть еще одно решение https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

, разрешающий запрос типа

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno,job' ) )

Вы не можете поместить динамический оператор в инструкцию PIVOT IN без использования PIVOT XML, который выводит несколько меньше желаемого результата. Тем не менее, вы можете создать строку IN и ввести ее в свой оператор.

Во-первых, вот моя таблица образцов;

  myNumber    myValue myLetter
---------- ---------- --------
         1          2 A        
         1          4 B        
         2          6 C        
         2          8 A        
         2         10 B        
         3         12 C        
         3         14 A      

Сначала настройте строку для использования в инструкции IN , Здесь вы помещаете строку в строку «str_in_statement». Мы используем COLUMN NEW_VALUE и LISTAGG для настройки строки.

clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT myLetter FROM myTable);

Ваша строка будет выглядеть так:

'A' AS A,'B' AS B,'C' AS C

Теперь используйте оператор String в вашем запросе PIVOT.

SELECT * FROM 
    (SELECT myNumber, myLetter, myValue FROM myTable)
    PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));

Вот вывод:

  MYNUMBER      A_VAL      B_VAL      C_VAL
---------- ---------- ---------- ----------
         1          2          4            
         2          8         10          6 
         3         14                    12 

Однако существуют ограничения. Вы можете конкатенировать строку до 4000 байт.

Я использовал вышеупомянутый метод (пользовательская функция Anton PL / SQL pivot ()), и он выполнил задание! Поскольку я не профессиональный разработчик Oracle, это простые шаги, которые я сделал:

1) Загрузите пакет zip, чтобы найти там pivotFun.sql. 2) Запустите один раз pivotFun.sql, чтобы создать новую функцию. 3) Используйте эту функцию в обычном SQL.

Просто будьте осторожны с именами динамических колонок. В моей среде я обнаружил, что имя столбца ограничено 30 символами и не может содержать в себе одну цитату. Итак, мой запрос теперь выглядит примерно так:

SELECT 
  *
FROM   
  table( 
        pivot('
                SELECT DISTINCT
                    P.proj_id,
                    REPLACE(substr(T.UDF_TYPE_LABEL, 1, 30), '''''''','','') as Attribute,
                    CASE
                      WHEN V.udf_text is null     and V.udf_date is null and      V.udf_number is NOT null  THEN to_char(V.udf_number)
                      WHEN V.udf_text is null     and V.udf_date is NOT null and  V.udf_number is null      THEN to_char(V.udf_date)
                      WHEN V.udf_text is NOT null and V.udf_date is null and      V.udf_number is null      THEN V.udf_text
                      ELSE NULL END
                    AS VALUE
                FROM
                    project   P
                LEFT JOIN UDFVALUE V ON P.proj_id     = V.proj_id 
                LEFT JOIN UDFTYPE  T ON V.UDF_TYPE_ID = T.UDF_TYPE_ID
                WHERE 
                    P.delete_session_id  IS NULL AND
                    T.TABLE_NAME = ''PROJECT''
    ')
)

Хорошо работает с записью до 1 м.