Если в pivot in больше 9 элементов в oracle запрос зависает
Измените CHARSET в поле индекса жалобы на «latin1», то есть ALTER TABLE tbl CHANGE myfield myfield varchar (600) CHARACTER SET latin1 DEFAULT NULL; latin1 принимает один байт за один символ вместо четырех
Вы не можете поместить не константную строку в предложение IN предложения о свопинге. Вы можете использовать Pivot XML для этого.
Подзапрос используется только в сочетании с ключевым словом XML. Когда вы указываете подзапрос, все значения, найденные подзапросом, используются для поворота
. Он должен выглядеть следующим образом:
Вместо этого также может быть подзапрос ключевого слова ANY :
Привет, ваш метод работает фактически, но вывод я получаю в формате xml. могу ли я получить вывод в виде таблицы со строками и столбцами? – prabhakar 19 March 2013 в 09:45
AFAIK, не динамически . Но как вы собираетесь использовать результат, который вы не знаете его структуры? – A.B.Cade 19 March 2013 в 10:09
ИСПОЛЬЗОВАТЬ ДИНАМИЧЕСКИЙ ЗАПРОС
Тестовый код ниже
Я не собираюсь давать ответ на вопрос, заданный OP, вместо этого я просто расскажу, как можно сделать динамический поворот.
Здесь мы должны использовать динамический sql, изначально извлекаем значения столбца в переменную и передачу переменной внутри динамического sql.
. У нас есть таблица, как показано ниже.
Если нам нужно показать значения в столбце YR как имена столбцов и значения в этих столбцах из QTY , тогда мы можем использовать приведенный ниже код.
При необходимости вы также можете создать временную таблицу и выполнить запрос выбора в этой временной таблице, чтобы увидеть результаты. Его простое, просто добавьте CREATE TABLE TABLENAME AS в приведенный выше код.
, разрешающий запрос типа
Вы не можете поместить динамический оператор в инструкцию PIVOT IN без использования PIVOT XML, который выводит несколько меньше желаемого результата. Тем не менее, вы можете создать строку IN и ввести ее в свой оператор.
Во-первых, вот моя таблица образцов;
Сначала настройте строку для использования в инструкции IN , Здесь вы помещаете строку в строку «str_in_statement». Мы используем COLUMN NEW_VALUE и LISTAGG для настройки строки.
Ваша строка будет выглядеть так:
Теперь используйте оператор String в вашем запросе PIVOT.
Однако существуют ограничения. Вы можете конкатенировать строку до 4000 байт.
при попытке этого я получаю ниже ошибки оракула: ORA-56900: переменная привязки не поддерживается внутри оси pivot | операция univot – lourdh 22 April 2016 в 05:50
Я использовал вышеупомянутый метод (пользовательская функция Anton PL / SQL pivot ()), и он выполнил задание! Поскольку я не профессиональный разработчик Oracle, это простые шаги, которые я сделал:
1) Загрузите пакет zip, чтобы найти там pivotFun.sql. 2) Запустите один раз pivotFun.sql, чтобы создать новую функцию. 3) Используйте эту функцию в обычном SQL.
Просто будьте осторожны с именами динамических колонок. В моей среде я обнаружил, что имя столбца ограничено 30 символами и не может содержать в себе одну цитату. Итак, мой запрос теперь выглядит примерно так:
Данные в реляционных базах данных иногда могут представлять собой иерархическую структуру. В этом случае одно поле таблицы является ссылкой на другую (родительскую) запись в той же самой таблице.
В Oracle Database для этого используется START WITH . CONNECT BY .
Для начала создадим таблицу с иерархическими данными. Пусть у нас есть таблица EMPLOYEES с тремя колонками:
- EMPLOYEE_ID
- LAST_NAME
- MANAGER_ID
Колонка MANAGER_ID ссылается на ту же самую таблицу EMPLOYEES . Вот скрипт её создания для Oracle:
Заполним её данными в соответствии со следующим деревом:
Иерархия сотрудников. Стрелки означают связь по полю MANAGER_ID.
SQL для заполнения этими данными:
Теперь вы можете выполнить запрос:
И получить список всех записей. С этим проблем нет.
Однако нам может потребоваться как-то учитывать иерархию при обработке данных с этой таблицей. С одним уровнем иерархии проблем нет. Например, следующий скрипт покажет всех сотрудников, напрямую подчиняющихся Сидорову:
Но как получить список всех сотрудников, которые находятся в подчинении Сидорова прямо или косвенно, то есть как получить часть ветки ниже Сидорова? Для этого нужно использовать START WITH . CONNECT BY :
В результате получим:
EMPLOYEE_ID | LAST_NAME | MANAGER_ID |
---|---|---|
2 | Сидоров | 1 |
4 | Васильев | 2 |
5 | Свиноухов | 2 |
6 | Литвина | 5 |
7 | Толстой | 5 |
В этом запросе с помощью START WITH мы задаём корень (начало) иерархии. С помощью CONNECT BY указываем, каким образом записи в иерархии связываются друг с другом в таблице и какие из них отобрать для результата. В CONNECT BY может быть несколько условий, но одно из них должно быть помечено PRIOR , чтобы указать, что именно оно указывает на родительский узел. С помощью ORDER SIBLINGS BY мы сортируем дочерние узлы каждого узла по отдельности (не весь результат!).
С помощью псевдоколонки LEVEL можно указывать уровень иерархии для каждой записи:
В версии 11g появились функции Pivot/Unpivot(которые сначала появились в MS SQL 2005), позволяющие динамически разносить вертикальные данные по столбцам как нам удобно.
Допустим у вас есть таблица customers:
показывает идентификатор заказчика, код штата, и сколько раз он что-либо покупал:
Нам нужно узнать количество заказчиков сгрупированных по каждому штату и по количеству их заказов:
select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;
Этот запрос выдает то, что нам нужно, но гораздо удобнее был бы в таком виде:
До версии 11g такое пришлось бы делать многократно повторяя sum(decode(state_code,'CT',1,0) «CT», sum(decode(state_code,'NY',1,0) «NY»,… Но благодаря функции pivot мы можем это сделать просто:
select * from (
select times_purchased as «Puchase Frequency», state_code
from customers t
)pivot(
count(state_code)
for state_code in ('NY' as "New York",'CT' «Connecticut»,'NJ' "New Jersey",'FL' «Florida»,'MO' as «Missouri»)
)
order by 1
/
Функция Unpivot совершает противоположные преобразования.
Тем же, кто еще не мигрировал на 11g, могу предложить свой модифицированный код Тома Кайта:
create or replace type varchar2_table as table of varchar2(4000);
/
create or replace package PKG_PIVOT is
function pivot_sql (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_head_sql in varchar2_table default varchar2_table()
)
return varchar2;
function pivot_ref (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_name in varchar2_table default varchar2_table()
)
return sys_refcursor;
end PKG_PIVOT;
/
create or replace package body PKG_PIVOT is
/**
* Function returning query
*/
function pivot_sql (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_head_sql in varchar2_table
) return varchar2
is
l_max_cols number;
l_query varchar2(4000);
l_pivot_name varchar2_table:=varchar2_table();
k integer ;
c1 sys_refcursor;
v varchar2(30);
begin
-- Получаем кол-во столбцов
if (p_max_cols_query is not null ) then
execute immediate p_max_cols_query
into l_max_cols;
else
raise_application_error (-20001, 'Cannot figure out max cols' );
end if ;
-- Собираем по кускам необходимый нам запрос
l_query := 'select ' ;
for i in 1 .. p_anchor. count loop
l_query := l_query || p_anchor (i) || ',' ;
end loop;
--Получаем названия колонок
k:=1;
if p_pivot_head_sql. count =p_pivot. count
then
for j in 1 .. p_pivot. count loop
open c1 for p_pivot_head_sql(j);
loop
fetch c1 into v;
l_pivot_name.extend(1);
l_pivot_name(k):=v;
EXIT WHEN c1%NOTFOUND;
k:=k+1;
end loop;
end loop;
end if ;
-- Добавляем колонки с полученными названиями
-- в виде "max(decode(rn,1,C,null)) c_name+1_1"
for i in 1 .. l_max_cols loop
for j in 1 .. p_pivot. count loop
l_query := l_query || 'max(decode(rn,' || i || ',' || p_pivot (j) || ',null)) '
|| '"' ||l_pivot_name ((j-1)*l_max_cols+i) || '"' || ',' ;
end loop;
end loop;
-- Вставляем исходный запрос
l_query := rtrim (l_query, ',' ) || ' from ( ' || p_query || ') group by ' ;
-- Группируем по колонкам
for i in 1 .. p_anchor. count loop
l_query := l_query || p_anchor (i) || ',' ;
end loop;
l_query := rtrim (l_query, ',' );
-- Возвращаем готовый SQL запрос
return l_query;
end ;
/**
* Функция возвращающая курсор на выполненный запрос
*/
function pivot_ref (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_name in varchar2_table
) return sys_refcursor
is
p_cursor sys_refcursor;
begin
execute immediate 'alter session set cursor_sharing=force' ;
open p_cursor for pkg_pivot.pivot_sql (
p_max_cols_query
, p_query
, p_anchor
, p_pivot
, p_pivot_name
);
execute immediate 'alter session set cursor_sharing=exact' ;
return p_cursor;
end ;
end PKG_PIVOT;
/
begin
:qq:=pkg_pivot.pivot_sql(
'select count(distinct trunc(dt)) from actions'
, 'select e.name name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'
, varchar2_table( 'NAME' )
, varchar2_table( 'SUM_CNT' )
, varchar2_table( 'select distinct ' 'Date ' '||trunc(dt) from actions' )
);
:qc :=pkg_pivot.pivot_ref(
'select count(distinct trunc(dt)) from actions'
, 'select e.name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'
, varchar2_table( 'NAME' )
, varchar2_table( 'SUM_CNT' )
, varchar2_table( 'select distinct ' 'Date ' '||trunc(dt) from actions' )
);
end ;
* This source code was highlighted with Source Code Highlighter .
Now B is of datatype varchar2 and X is a string of varchar2 values separated by commas.
Values for X are select distinct values from a column(say CL) of same table. This way pivot query was working.
But the problem is that whenever there is a new value in column CL I have to manually add that to the string X.
I tried replacing X with select distinct values from CL. But query is not running.
The reason I felt was due to the fact that for replacing X we need values separated by commas.
Then i created a function to return exact output to match with string X. But query still doesn't run.
The error messages shown are like "missing righr parantheses", "end of file communication channel" etc etc.
I tried pivot xml instead of just pivot, the query runs but gives vlaues like oraxxx etc which are no values at all.
Maybe I am not using it properly.
Can you tell me some method to create a pivot with dynamic values?
10 Answers 10
You cannot put a dynamic statement in the PIVOT's IN statement without using PIVOT XML, which outputs some less than desirable output. However, you can create an IN string and input it into your statement.
First, here is my sample table;
First setup the string to use in your IN statement. Here you are putting the string into "str_in_statement". We are using COLUMN NEW_VALUE and LISTAGG to setup the string.
Your string will look like:
Now use the String statement in your PIVOT query.
Here is the Output:
There are limitations though. You can only concatenate a string up to 4000 bytes.
while trying this i am getting below oracle error: ORA-56900: bind variable is not supported inside pivot|unpivot operation
You can't put a non constant string in the IN clause of the pivot clause.
You can use Pivot XML for that.
subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting
It should look like this:
You can also have a subquery instead of the ANY keyword:
hi your method is working actually but output i am getting in xml format. can i get output as a table with rows and columns ?
I was hoping to use it against another dynamic SQL, but you have a good point about knowing the structure.
allowing a query like
I am not exactly going to give answer for the question OP has asked, instead I will be just describing how dynamic pivot can be done.
Here we have to use dynamic sql, by initially retrieving the column values into a variable and passing the variable inside dynamic sql.
EXAMPLE
Consider we have a table like below.
If we need to show the values in the column YR as column names and the values in those columns from QTY , then we can use the below code.
RESULT
If required, you can also create a temp table and do a select query in that temp table to see the results. Its simple, just add the CREATE TABLE TABLENAME AS in the above code.
Error report - ORA-06550: line 5, column 74: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 5, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
USE DYNAMIC QUERY
Test code is below
I used the above method (Anton PL/SQL custom function pivot()) and it done the job! As I am not a professional Oracle developer, these are simple steps I've done:
1) Download the zip package to find pivotFun.sql in there. 2) Run once the pivotFun.sql to create a new function 3) Use the function in normal SQL.
Just be careful with dynamic columns names. In my environment I found that column name is limited with 30 characters and cannot contain a single quote in it. So, my query is now something like this:
Works well with up to 1m records.
You cannot put a dynamic statement in the PIVOT's IN statement without using PIVOT XML, but you can use small Technic to use dynamic statement in PIVOT. In PL/SQL, within a string value, two apostrophe is equal to one apostrophes.
There’s no straightforward method for dynamic pivoting in Oracle’s SQL, unless it returns XML type results. For the non-XML results PL/SQL might be used through creating functions of SYS_REFCURSOR return type
With Conditional Aggregation
With PIVOT Clause
But there's a drawback with LISTAGG() that's coded ORA-01489: result of string concatenation is too long raises whenever the concatenated string within the first argument exceeds the length of 4000 characters. In this case, the query returning the value of v_cols variable might be replaced with the XMLELEMENT() function nested within XMLAGG() such as
unless the upper limit 32767 for VARCHAR2 type is exceeded. This last method might also be applied for the database with version prior to Oracle 11g Release 2 as they don't contain LISTAGG() function.
Btw, yet LISTAGG() function can be used during the checkout of the v_cols even for very long concatenated string generated without getting ORA-01489 error while the trailing part of the string is truncated through use of ON OVERFLOW TRUNCATE clause if the version for the database is 12.2+ such as
The function can be invoked as
from SQL Developer's command line
or
from Test window of PL/SQL Developer in order to get the result set.
I have the below table. I need to create columns based off the column CCL. The values in column CCL are unknown. I'm not sure where to begin here. Any help would be appreciated.
There are essentially three choices here. (1) Don't do it. (2) If you need to do it, and it's only for a final report, then use a reporting front-end that can do the pivoting. That is where "dynamic pivoting" (which is what this is called) belongs, in most cases. (3) If you need to do this because you need the result in further calculations, then be advised that this is an advanced topic. It has to do with dynamic SQL, something that should only be used as a last resort, and it is definitely not for beginners. So, if you are a beginner, don't try it!
Very good article on the subject, with ready-made PL/SQL API for this: technology.amis.nl/2006/05/24/…. I have used it and it works in most cases. E.g., don't try to use it in dynamic SQL.
2 Answers 2
Using dynamic sql for a result where the columns are unknown at the time of executing is a bit of a hassle in Oracle compared to certain other RDMBS.
Because the record type for the output is yet unknown, it can't be defined beforehand.
In Oracle 11g, one way is to use a nameless procedure that generates a temporary table with the pivoted result.
Then select the results from that temporary table.
Returns:
You can find a test on db<>fiddle here
In Oracle 11g, another cool trick (created by Anton Scheffer) to be used can be found in this blog. But you'll have to add the pivot function for it.
The source code can be found in this zip
After that the SQL can be as simple as this:
You'll find a test on db<>fiddle here
Side note. If you're using Oracle 12c then have a look at this article. They added some functionality to the DBMS_SQL package in 12c to facilitate migrations to Oracle. I couldn't find an online tester for Oracle 12c, so can't verify. But I think you can just run a dynamic PIVOT with that without going via a temporary table.
you can do that with any client application which can fetch ref cursor and without creating any objects as shown in the end of my answer. That is possible starting with Oracle 7 which was released in 1992 which is 26 years ago. Implicit Results have nothing to do with dynamic SQL, just in case.
Oracle must know all the column in select list on PARSING stage.
This has a couple of consequences
It's not possible for Oracle to change the column list of the query without re-parsing it. Regardless what is supposed to impact that - whether it's distinct list of values in some column or something else. In other words you cannot expect Oracle to add new columns to output if you added new value to CCL column in your example.
In each and every query you must specify explicitly all the columns in select list unless you use "*" with table alias. If you use "*" then Oracle gets column list from metadata and if you modify metadata (i.e. run DDL on a table) then Oracle re-parses query.
So the best option to deal with "Dynamic Pivoting" is to pivot and format result in the UI. However, there are still some options in database which you may want to consider.
Generating XML with pivoted result and parsing it.
Do pivot for XML and then parse results. In this case, eventually, you have to specify pivoted columns one way or another.
You may have noticed 2 important details
In fact, each pivoted column is represented using two columns in result - one for caption and one for value
Names are ordered so you cannot preserver order like in your example ('john', 'adam', 'terry', 'rob'), moreover one column may represent different names like NAME1 represents values for 'adam' in first row and 'john' in second row.
It's possible to use only indices to get the same output.
But still there are two columns for each pivoted column in the output.
Below query returns exactly the same data as in your example
But wait. all the values for CCL are specified in the query. This is because column caption cannot depend on the data in the table. So what is the point in pivoting for XML if you could have just hardcoded all values in for clause with the same success? One of the ideas is that Oracle SQL engine transposes query result and the tool which displays output just has to properly parse XML. So you split pivoting logic into two layers. XML parsing can be done outside SQL, say, in your application.
ODCI table interface
There is already a link in another answer to Anton's solution. You can also check an example here. And, of course, it's explained in detail in Oracle Documentation.
Polymorphic Table Functions
One more advanced technology has been introduces in Oracle 18 - Polymorphic Table Functions. But again, you should not expect that column list of your query will change after you added new value to CCL. It can change only after re-parsing. There is a way to force hard parse before each excution, but that is another topic.
Dynamic SQL
Finally, as also already pointed out in the comments, you can use good old DSQL. First step - generate SQL statement based on the table contents. Second step - execute it.
Читайте также: