рдорд╛рди рд▓реАрдЬрд┐рдП рдХрд┐ рдЖрдкрдХреЗ рдкрд╛рд╕ рдЧреНрд░рд╛рд╣рдХ рддрд╛рд▓рд┐рдХрд╛ рд╣реИ:
SQL> desc рдЧреНрд░рд╛рд╣рдХ рдирд╛рдо рдирд▓? рдЯрд╛рдЗрдк ----------------------------------------- -------- - -------------------------- CUST_ID NUMBER (10) CUST_NAME VARCHAR2 (20) STATE_CODE VARCHAR2 (2) TIMES_PURCHASED NUMBER (3)
рдирдореВрдирд╛ рдХрд╣рд╛рдВ рд╣реИ
cust_id, state_code, times_purchased рдХрд╛ рдЪрдпрди рдХрд░реЗрдВ
рдЧреНрд░рд╛рд╣рдХреЛрдВ рд╕реЗ
cust_id рджреНрд╡рд╛рд░рд╛ рдЖрджреЗрд╢ ;
рдЧреНрд░рд╛рд╣рдХ рдХреА рдкрд╣рдЪрд╛рдирдХрд░реНрддрд╛, рд░рд╛рдЬреНрдп рдХреЛрдб, рдФрд░ рдХрд┐рддрдиреА рдмрд╛рд░ рдЙрд╕рдиреЗ рдХреБрдЫ рдЦрд░реАрджрд╛ рд╣реИ:
CUST_ID STATE_CODE TIMES_PURCHASED ------- ---------- --------------- рез рд╕реАрдЯреА рез реи рдПрдирд╡рд╛рдИ резреж рей рдПрдирдЬреЗ реи 4 рдПрдирд╡рд╛рдИ 4
рд╣рдореЗрдВ рдкреНрд░рддреНрдпреЗрдХ рд░рд╛рдЬреНрдп рдХреЗ рд▓рд┐рдП рд╕рдореВрд╣реАрдХреГрдд рдЧреНрд░рд╛рд╣рдХреЛрдВ рдХреА рд╕рдВрдЦреНрдпрд╛ рдФрд░ рдЙрдирдХреЗ рдЖрджреЗрд╢реЛрдВ рдХреА рд╕рдВрдЦреНрдпрд╛ рдХрд╛ рдкрддрд╛ рд▓рдЧрд╛рдирд╛ рд╣реЛрдЧрд╛:
рд░рд╛рдЬреНрдп_рдХреЛрдб, times_purchased, рдЧрд┐рдирддреА (1) рдХрд╛ рдЪрдпрди рдХрд░реЗрдВ
рдЧреНрд░рд╛рд╣рдХреЛрдВ рд╕реЗ
State_code, times_purchased рджреНрд╡рд╛рд░рд╛ рд╕рдореВрд╣;
ST TIMES_PURCHASED CNT - --------------- ---------- рд╕реАрдЯреА реж реп реж рд╕реАрдЯреА 1 165 рд╕реАрдЯреА 2 179 рд╕реАрдЯреА 3 173 рд╕реАрдЯреА 4 173 рд╕реАрдЯреА 5 152 ...
рдпрд╣ рдХреНрд╡реЗрд░реА рд╣рдореЗрдВ рдЬреЛ рдЪрд╛рд╣рд┐рдП, рд╡рд╣ рд▓реМрдЯрд╛рддреА рд╣реИ, рд▓реЗрдХрд┐рди рдпрд╣ рдЗрд╕ рд░реВрдк рдореЗрдВ рдмрд╣реБрдд рдЕрдзрд┐рдХ рд╕реБрд╡рд┐рдзрд╛рдЬрдирдХ рд╣реЛрдЧреА:
Times_purch CT NY NJ ... 1 0 1 0 0 ... 2 23 119 37 ... 3 17 45 1 ... ...
рд╕рдВрд╕реНрдХрд░рдг 11 рдЬреА рд╕реЗ рдкрд╣рд▓реЗ, рдпрд╣ рдмрд╛рд░-рдмрд╛рд░ рдпреЛрдЧ (рдбрд┐рдХреЛрдб (рд╕реНрдЯреЗрдЯ_рдХреЛрдб, 'рд╕реАрдЯреА', 1,0) "рд╕реАрдЯреА", рд╕рдо (рдбрд┐рдХреЛрдб (рд╕реНрдЯреЗрдЯ_рдХреЛрдб, 'рдПрдирд╡рд╛рдИ', 1,0) "рдПрдирд╡рд╛рдИ", ... рдХреЛ рджреЛрд╣рд░рд╛рдпрд╛ рдЬрд╛рдПрдЧрд╛ред рд╣рдо рдЗрд╕реЗ рдмрд╕ рдХрд░ рд╕рдХрддреЗ рд╣реИрдВ:
рд╕реЗ рдЪреБрдиреЗрдВ *
"Puchase рдЖрд╡реГрддреНрддрд┐", state_code рдХреЗ рд░реВрдк рдореЗрдВ time_purchased рдХрд╛ рдЪрдпрди рдХрд░реЗрдВ
рдЧреНрд░рд╛рд╣рдХреЛрдВ рд╕реЗ рдЯреА
) рдзреБрд░реА (
рдЧрд┐рдирддреА (State_code)
State_code рдХреЗ рд▓рд┐рдП ("NY" рдХреЛ "рдиреНрдпреВрдпреЙрд░реНрдХ", 'CT' "рдХрдиреЗрдХреНрдЯрд┐рдХрдЯ", 'NJ' "рдиреНрдпреВ рдЬрд░реНрд╕реА", 'FL' "рдлреНрд▓реЛрд░рд┐рдбрд╛", "MO" рдХреЛ "рдорд┐рд╕реМрд░реА" рдХреЗ рд░реВрдк рдореЗрдВ)
)
1 рджреНрд╡рд╛рд░рд╛ рдЖрджреЗрд╢
/
Puchase рдЖрд╡реГрддреНрддрд┐ рдиреНрдпреВ рдпреЙрд░реНрдХ рдХрдиреЗрдХреНрдЯрд┐рдХрдЯ рдиреНрдпреВ рдЬрд░реНрд╕реА рдлреНрд▓реЛрд░рд┐рдбрд╛ рдорд┐рд╕реМрд░реА ----------------- ---------- ----------- ---------- - -------- ---------- 0 16 601 90 0 0 0 1 33048 165 0 0 0 2 33 151 179 0 0 0 3 32 978 173 0 0 0 4 33 109 173 0 1 0 ...
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{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 .
рдкрд░рд┐рдгрд╛рдо:
