create or replace procedure line_to_col(days in integer) as
v1 varchar2(10);
t1 varchar2(2) := 'DD';
t2 varchar2(10):= 'yyyy/mm/dd';
sqlstr varchar2(32767) := 'create or replace view xxm7 as select languageid';
begin
for xx in 1..days loop
v1 := to_char(trunc(sysdate)-days+xx,t2);
sqlstr := sqlstr || ',sum(decode(createdate,to_date(''' || v1 || ''','''||t2||'''),total,0)) tota'||xx||'
,sum(decode(createdate,to_date(''' || v1 || ''','''||t2||'''),Basic,0)) Basic'||xx||'
,sum(decode(createdate,to_date(''' || v1 || ''','''||t2||'''),CreditTest,0)) CreditTest'||xx||'
,sum(decode(createdate,to_date(''' || v1 || ''','''||t2||'''),CreditDepthTest,0)) CreditDepthTest'||xx;
end loop;
sqlstr := sqlstr||'
from (select tcs.language_ID languageId,
trunc(tcs.create_date) createdate,
count(tcs.store_level) total,
sum(decode(tcs.store_level, 0, 1, 0)) Basic,
sum(decode(tcs.store_level, 1, 1, 0)) CreditTest,
sum(decode(tcs.store_level, 2, 1, 0)) CreditDepthTest
from TO_COUNT_STORE tcs
where tcs.create_date >= trunc(sysdate - '||days||' + 1,'''||t1||''')
group by language_id, trunc(tcs.create_date))
group by languageid';
execute immediate sqlstr;
end line_to_col;