Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table TableA (
ID int,
CCL varchar2(10), 
Flag char(1)
);
insert all
into tableA values (1, 'john', 'x')
into tableA values (1, 'adam', 'x')
into tableA values (1, 'terry', null)
into tableA values (1, 'rob', 'x')
into tableA values (2, 'john', 'x')
select * from dual;
5 rows affected

declare
  v_sqlqry clob;
  v_cols clob;
begin
  -- Generating a string with a list of the unique names
  select listagg(''''||CCL||''' as "'||CCL||'"', ', ') within group (order by CCL)
  into v_cols
  from 
  (
    select distinct CCL
    from tableA
  );

  -- drop the temporary table if it exists
  EXECUTE IMMEDIATE 'DROP TABLE tmpPivotTableA';
  EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;

  -- A dynamic SQL to create a temporary table 
  -- based on the results of the pivot
  v_sqlqry := '
    CREATE GLOBAL TEMPORARY TABLE tmpPivotTableA
    ON COMMIT PRESERVE ROWS AS
    SELECT * 
    FROM (SELECT ID, CCL, Flag FROM TableA) src 
    PIVOT (MAX(Flag) FOR (CCL) IN ('||v_cols||')) pvt';

  dbms_output.Put_line(v_sqlqry); -- just to check how the sql looks like
  execute immediate v_sqlqry;
  
end;
/
1 rows affected

dbms_output:

    CREATE GLOBAL TEMPORARY TABLE tmpPivotTableA
    ON COMMIT PRESERVE ROWS AS
    SELECT * 
    FROM (SELECT ID, CCL, Flag FROM TableA) src 
    PIVOT (MAX(Flag) FOR (CCL) IN ('adam' as "adam", 'john' as "john", 'rob' as "rob", 'terry' as "terry")) pvt

select * from tmpPivotTableA;


ID
adam
john
rob
terry


1
x
x
x
null

2
null
x
null
null
ID	adam	john	rob	terry
1	x	x	x	null
2	null	x	null	null