|
не пойму как тут можно выкрутиться.
#39272570
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Pakistan
Сообщения: 729
|
|
1:
Click
2:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
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{X+1},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.
|
|
|
