CREATE TABLE zzz_main_src_test
(
nmemid VARCHAR2(10),
colnum VARCHAR2(10),
col_value VARCHAR2(10)
);

BEGIN
FOR a IN 1..6667 LOOP
  FOR b IN 1..450 LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Insert into zzz_main_src_test(nmemid, colnum, col_value)    values('''||a||''','''||b||''','||Round(Dbms_Random.value()*100,4)||')';
    END;
  END LOOP;
  COMMIT;
END LOOP;
END;

DECLARE
CURSOR cur_collist IS
SELECT DISTINCT 'C_'||colnum AS colname FROM zzz_main_src_test;
BEGIN
  BEGIN EXECUTE IMMEDIATE 'DROP TABLE zzz_450col_test PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
  EXECUTE IMMEDIATE 'CREATE TABLE zzz_450col_test (nmemid varchar2(10))';
  FOR a IN cur_collist LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE zzz_450col_test ADD  '||a.colname||' VARCHAR2(10)';
    END;
  END LOOP;
END;

create table keys as
	select rownum as id
	from dual
	connect by rownum <= 6666
/

create table cols as
	select 'C'||lpad(rownum,3,'0') as col_name
	from dual
	connect by rownum <= 450
/


create table vals as
	select id,col_name,dbms_random.value as val
	from keys
		cross join cols
/

create index vals_dx on vals (
	id,col_name
)
/

create or replace type string_table as table of varchar2(4000)
/
CREATE OR REPLACE FUNCTION tab2clob (
	tab in string_table, 
	delimitatore in varchar2  
) return clob 
as
	out_string CLOB;
begin
	out_string := NULL;
	if ( cardinality(tab) > 0 ) then
		for i in tab.first .. tab.last loop
			if ( i != tab.first ) then
				if ( delimitatore is not null ) then
					DBMS_LOB.writeappend(OUT_STRING,LENGTh(delimitatore),delimitatore);
				end if;
			else
				dbms_lob.createtemporary(OUT_STRING,true);
			end if;
			IF ( TAB(I) IS NOT NULL ) then
				DBMS_LOB.writeappend(OUT_STRING,LENGTh(tab(i)),tab(i));
			end if;
		end loop;
		return out_string;
	else
		return null;
	end if;
end;
/

select /*with a lot of RAM for SORT_AREA this would be really efficient*/
'select id,
'||tab2clob(
		cast(collect(
		'		max(decode(col_name,'''||col_name||''',val)) as '||col_name
		) as string_table),
		',
'
	)||'
from vals
group by id'
from cols
/

select /*Immediately fetches the first rows and doesn't require too much RAM to be efficient*/
'select id,
'||tab2clob(
		cast(collect(
		'		(select val from vals v where v.id=k.id and v.col_name='''||col_name||''') as '||col_name
		) as string_table),
		',
'
	)||'
from keys k
group by id'
from cols
/

select id,
		max(decode(col_name,'C001',val)) as C001,
		max(decode(col_name,'C002',val)) as C002,
		max(decode(col_name,'C003',val)) as C003,
		...
		...
		...
		max(decode(col_name,'C449',val)) as C449,
		max(decode(col_name,'C450',val)) as C450
from vals
group by id
/

select id,
		(select val from vals v where v.id=k.id and v.col_name='C001') as C001,
		(select val from vals v where v.id=k.id and v.col_name='C002') as C002,
		(select val from vals v where v.id=k.id and v.col_name='C003') as C003,
		...
		...
		...
		(select val from vals v where v.id=k.id and v.col_name='C449') as C449,
		(select val from vals v where v.id=k.id and v.col_name='C450') as C450
from keys k
group by id
/