Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 транспонировать строку в столбец  [new]
anetgav
Member

Откуда:
Сообщений: 15
Доброе время суток!

Подскажите, пожалуйста....
Есть задача, транспонировать строку в столбец. Решили с помощью пакета pk_crosstab.Выполняя запрос в результате получаю еще один запрос...

Для примера:

select pk_crosstab.PivotSQL('select owner,tablespace_name,table_name from all_tables','owner','tablespace_name','count','table_name') from dual

Результат:

SELECT owner,
COUNT (DECODE (tablespace_name, 'ADMIN', table_name, NULL)) AS "ADMIN",
COUNT (DECODE (tablespace_name, 'ANALIZ', table_name, NULL))
AS "ANALIZ",
COUNT (DECODE (tablespace_name, 'ETALON', table_name, NULL))
AS "ETALON",
COUNT (DECODE (tablespace_name, 'SYSAUX', table_name, NULL))
AS "SYSAUX",
COUNT (DECODE (tablespace_name, 'SYSTEM', table_name, NULL))
AS "SYSTEM",
COUNT (DECODE (tablespace_name, 'USERS', table_name, NULL)) AS "USERS",
COUNT (DECODE (tablespace_name, NULL, table_name, NULL)) AS "==NULL=="
FROM (SELECT owner, tablespace_name, table_name FROM all_tables)
GROUP BY owner
ORDER BY owner

И в итоге необходимые данные.

Как с помощью одного ВЫПОЛНИТЬ получить необходимые данные?

P.S. oracle 10g.
7 июл 11, 16:15    [10940083]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
Alexander Konakov
Member

Откуда:
Сообщений: 1027
anetgav,

Dynamic SQL + Pipelined function?
7 июл 11, 16:27    [10940201]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
Alexander Konakov
Member

Откуда:
Сообщений: 1027
Alexander Konakov
anetgav,

Dynamic SQL + Pipelined function?

Хотя вряд ли - для pipelined надо знать возвращаемый тип.
7 июл 11, 16:28    [10940207]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
anetgav
Member

Откуда:
Сообщений: 15
Alexander Konakov,

CREATE OR REPLACE PACKAGE BODY PK_CROSSTAB AS
FUNCTION PivotSQL (
--вернуть текст запроса для получения crosstab
p_query IN varchar2,--
p_rowfields IN varchar2,
p_columnfield IN varchar2,
p_function IN varchar2,
p_functionfield IN varchar2
) RETURN varchar2
AS
l_max_cols number;
l_query long;
TYPE array_varchar2 IS TABLE OF varchar2(255);
l_columnnames array_varchar2 :=array_varchar2();
l_cursor refcursor;
tmp long;
--dbms_sql types:
l_theCursor integer DEFAULT dbms_sql.open_cursor;--get col types
l_colCnt number DEFAULT 0;
l_descTbl dbms_sql.desc_tab;
col_num number;
l_columnfieldtype number;
--decode names
o_decode varchar2(50);
o_col varchar2(50);
ft UTL_FILE.file_type;

BEGIN
--dbms_output.enable(20000000);
--check params
ft:=UTL_FILE.fopen('C_TEMP','my_file.txt','W');
IF INSTR(p_columnfield,',')>0 THEN
RAISE_APPLICATION_ERROR (-20001, 'Can use only 1 columnfield');
ELSIF UPPER(p_function) NOT IN ('SUM','AVG','COUNT','MIN','MAX') THEN
RAISE_APPLICATION_ERROR (-20001, 'Can use only standard aggregate functions');
END IF;

/* Шаг 2: проанализировать запрос, чтобы можно было получить описание его результатов. */
dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
/* Шаг З: получаем описание результатов запроса. */
dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);

/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := l_descTbl.FIRST;
LOOP
exit WHEN (col_num IS NULL);
--find column field type
IF l_descTbl(col_num).col_name=UPPER(p_columnfield) THEN
l_columnfieldtype:=l_descTbl(col_num).col_type;
--dbms_output.put_line('Col#:'||col_num||' Name:'||l_descTbl(col_num).col_name||' Type:'||l_descTbl(col_num).col_type);
END IF;
col_num := l_descTbl.NEXT(col_num);
END LOOP;
--return 'test ok';

-- figure out the column names we must support for horizontal cross
IF (p_columnfield IS NOT NULL) THEN
tmp:='SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ') ORDER BY ' || p_columnfield;
-- dbms_output.put_line('columns cursor:'||tmp);
OPEN l_cursor FOR tmp;
LOOP
l_columnnames.EXTEND;
FETCH l_cursor INTO l_columnnames(l_columnnames.COUNT);
--dbms_output.put_line('l_columnnames:'||l_columnnames(l_columnnames.COUNT));
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
CLOSE l_cursor;

-- execute immediate 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')' bulk collect into l_columnnames ;

ELSE
RAISE_APPLICATION_ERROR (-20001, 'Cannot figure out max cols');
END IF;

-- Now, construct the query that can answer the question for us...
l_query := 'SELECT ' || p_rowfields ;
FOR i IN 1 .. l_columnnames.COUNT-1 LOOP
FormatParam(l_columnnames(i),l_columnfieldtype, o_decode, o_col);--format params
l_query := l_query || ',' || p_function || '(DECODE(' || p_columnfield || ',' || o_decode || ','|| p_functionfield ||',null)) as "'|| o_col ||'" ' ; --" для строк с пробелами
END LOOP;
l_query := l_query || ' FROM (' || p_query || ')';

l_query := l_query || ' GROUP BY ' || p_rowfields || ' ORDER BY ' || p_rowfields;
/* Step 9: закрываем курсор, чтобы освободить ресурсы. */
dbms_sql.close_cursor(l_theCursor);
UTL_FILE.put_line(ft,'end_end');
UTL_FILE.fCLOSE(ft);
-- and return it
--dbms_output.put_line('l_query:'||l_query);
RETURN l_query;
--Поскольку вполне вероятно, что
--в условии запроса есть константы, мы включаем опцию cursor_sharing перед анализом
--запроса, чтобы принудительно использовались связываемые переменные, а затем отключаем ее.
/* execute immediate 'alter session set cursor_sharing=force';
open p_cursor for l_query;
execute immediate 'alter session set cursor_sharing=exact';
*/

EXCEPTION
WHEN OTHERS THEN
/* Step 9: закрываем курсор, чтобы освободить ресурсы. */
dbms_sql.close_cursor(l_theCursor);
RAISE_APPLICATION_ERROR (-20001,'Ошибка в PivotSQL:' || SQLERRM);
END;
--=========================

PROCEDURE FormatParam (var_data IN varchar2, var_type IN number, out_decode IN OUT varchar2, out_col IN OUT varchar2)
--форматировать параметр в соотв с типом для PivotSQL
--принять текст параметра и его тип
-- выдать строки для decode и имени колонки
/* типы dbms_sql.describe_columns :
DATE Type:12
Varchar2 Type:1
Number Type:2
*/
IS
ft UTL_FILE.file_type;
BEGIN
ft:=UTL_FILE.fopen('C_TEMP','my_format.txt','W');
UTL_FILE.put_line(ft,'VAR_DATA='||var_data);
UTL_FILE.put_line(ft,'VAR_type='||var_type);
IF var_data IS NULL THEN--если в колонку выпал null
out_decode:='NULL';
out_col:='==NULL==';
--данный case не перепутается с текстовым значением 'NULL' столбца varchar - будет две разных колонки

ELSIF var_type = 1 THEN -- Varchar2
out_decode:=''''||var_data||'''';--add quotes
out_col:=SUBSTR(var_data,1,30);

ELSIF var_type = 2 THEN --Number
out_decode:=var_data;--do nothing
out_col:=SUBSTR(var_data,1,30);

ELSIF var_type = 12 THEN --DATE
out_decode:='to_date('''||var_data||''',''dd.mm.yyyy'')';--format as internal date
out_col:=TO_CHAR(TO_DATE(var_data));
UTL_FILE.put_line(ft,'out_decode='||out_decode);
UTL_FILE.put_line(ft,'out_col='||out_col);
UTL_FILE.fCLOSE(ft);
ELSE
out_decode:='== UNDEFINED TYPE:'||var_type;
out_col:='== UNDEFINED TYPE';
END IF;


EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,'Ошибка в FormatParam:' || SQLERRM);
END;
END;
/

Да.
7 июл 11, 16:42    [10940327]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
anetgav
Member

Откуда:
Сообщений: 15
Пакет взят у Alexus12. Спасибо, ему за это!!!
https://www.sql.ru/forum/actualthread.aspx?tid=409886&pg=-1
7 июл 11, 16:55    [10940460]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
Alexander Konakov
Member

Откуда:
Сообщений: 1027
anetgav,

этот пакет попадался на AskTom - но он ведь не решает проблему?
7 июл 11, 17:56    [10940947]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
anetgav
Member

Откуда:
Сообщений: 15
Он транспонирует строку в столбец, но до результата доходит за 2 шага.
А нужно за один....
7 июл 11, 18:07    [10941020]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
anetgav,
Если производительность не критична и есть тяга к экспериментам, то можно задействовать XML. Например, одним из способов описанных здесь: 10374374.

З.Ы. А вообще про транспонирование есть в топе популярных вопросов здесь если мне не изменяет склероз.
З.З.Ы. Если выкладываешь какой-то код - пользуйся тегом SRC.
7 июл 11, 19:26    [10941392]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать строку в столбец  [new]
anetgav
Member

Откуда:
Сообщений: 15
dbms_photoshop
anetgav,
Если производительность не критична и есть тяга к экспериментам, то можно задействовать XML. Например, одним из способов описанных здесь: 10374374.

З.Ы. А вообще про транспонирование есть в топе популярных вопросов здесь если мне не изменяет склероз.
З.З.Ы. Если выкладываешь какой-то код - пользуйся тегом SRC.


В способе для задействования XML также написано, количество строк имеет значение. Для 10 - критично 2500 строк, а у меня больше.

зы Учту, спасибо)
8 июл 11, 15:02    [10946131]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить