--- example1 table has the following structure: -----------------
create table EXAMPLE1
(
PROFILEID NUMBER(10) not null,
MONTHFIELD VARCHAR2(6),
COUNTHOURS NUMBER
)
-- Step 1 generate the decode rows for the table
select 'sum(decode(monthfield,'''|| v||''',counthours,0)) C'||v||',' from
(select distinct monthfield v from EXAMPLE1 order by v);
-- Step 2 paste the generated code below
select profileid,
-- paste here :
sum(decode(monthfield,'200407',counthours,0)) C200407,
sum(decode(monthfield,'200408',counthours,0)) C200408,
sum(decode(monthfield,'200507',counthours,0)) C200507,
sum(decode(monthfield,'200508',counthours,0)) C200508,
sum(decode(monthfield,'200509',counthours,0)) C200509,
sum(decode(monthfield,'200510',counthours,0)) C200510,
sum(decode(monthfield,'200511',counthours,0)) C200511,
sum(decode(monthfield,'200512',counthours,0)) C200512,
sum(decode(monthfield,'200601',counthours,0)) C200601,
sum(decode(monthfield,'200602',counthours,0)) C200602,
sum(decode(monthfield,'200603',counthours,0)) C200603,
sum(decode(monthfield,'200604',counthours,0)) C200604,
sum(decode(monthfield,'200605',counthours,0)) C200605,
sum(decode(monthfield,'200606',counthours,0)) C200606,
sum(decode(monthfield,'200607',counthours,0)) C200607,
sum(decode(monthfield,'200608',counthours,0)) C200608,
sum(decode(monthfield,'200609',counthours,0)) C200609,
sum(decode(monthfield,'200610',counthours,0)) C200610,
sum(decode(monthfield,'200611',counthours,0)) C200611,
sum(decode(monthfield,'200612',counthours,0)) C200612,
sum(decode(monthfield,'200701',counthours,0)) C200701
-- end paste
from example1 t
group by profileid ;