How to Unpivot with dynamic columns Oracle

You can use dbms_output.put_line(sql_stmt) to see the actual dynamic SQL being generated, which in this case is:

SELECT PK,

    VarName,
    Valuer,
    Max(timestamp) over (Partition by PK) as timestamp,

    FROM   test
    UNPIVOT(Valuer FOR VarName IN ('COL1' as "COL1",'COL2' as "COL2",'COL3' as "COL3",'COL4' as "COL4",'COL5' as "COL5",'COL6' as "COL6",'PK' as "PK",'TIMESTAMP' as "TIMESTAMP"))

Which has a number of issues. Generally for this sort of thing it's sensible to start from a static statement you know works and then figure out how to make it dynamic, but you don't seem to have done that here.

This version gets ORA-00936: missing expression because of the trailing comma after the timestamp in the dynamic statement. Which I imagine is another error from modifying your code for posting. Without that it gets the ORA-00904: : invalid identifier you have in your question. The immediate cause of that error you're getting is the parts in the parentheses, such as:

'COL1' as "COL1"

The quotes are wrong; that should be:

COL1 as 'COL1'

Fixing that then gives ORA-00904: "PK": invalid identifier because your column look-up isn't excluding the columns you don't want to pivot on, so really you want:

  select listagg(column_name || ' as ''' || column_name || '''', ',')
    within group (order by column_name) 
  into   pivot_clause
  from user_tab_columns
  where table_name = 'TEST'
  and column_name not in ('PK', 'TIMESTAMP');

Your next problem is that the columns you're unpivoting are different data types, so you get ORA-01790: expression must have same datatype as corresponding expression. That's a bit trickier - essentially you'll have to convert everything to strings, using suitable formats, particularly if there are dates involved. You need to do that conversion in a subquery, and you unpivot the result of that. An example that just explicitly handles the number column, generating the subquery columns in the same way as the unpivot clause:

declare
  sql_stmt     clob;
  subquery     clob;
  pivot_clause clob;
begin
  select listagg(column_name || ' as ''' || column_name || '''', ',')
      within group (order by column_name),
    listagg(
    case when data_type = 'NUMBER' then 'to_char(' || column_name || ')'
      else column_name
      end || ' as ' || column_name, ',')
      within group (order by column_name)
  into   pivot_clause, subquery
  from user_tab_columns
  where table_name = 'TEST'
  and column_name not in ('PK', 'TIMESTAMP');

  sql_stmt := 'select pk,
    varname,
    valuer,
    max(timestamp) over (partition by pk) as timestamp
    from (select pk, timestamp, ' || subquery || ' from test)
    unpivot(valuer for varname in (' || pivot_clause || '))';

  dbms_output.put_line(sql_stmt);
  execute immediate sql_stmt;
end;
/

You could instead always cast every column to say varchar2(255) in the second listagg(), but then yuo have no direct control over formatting and are reliant on NLS settints.

When run the block above now generates:

select pk,
    varname,
    valuer,
    max(timestamp) over (partition by pk) as timestamp
    from (select pk, timestamp, COL1 as COL1,COL2 as COL2,COL3 as COL3,COL4 as COL4,COL5 as COL5,to_char(COL6) as COL6 from test)
    unpivot(valuer for varname in (COL1 as 'COL1',COL2 as 'COL2',COL3 as 'COL3',COL4 as 'COL4',COL5 as 'COL5',COL6 as 'COL6'))

and when run manually that gets output like:

PK  VARNAME  VALUER   TIMESTAMP                     
1   COL1     value1   11-AUG-17 15.49.42.239283000  
1   COL2     value2   11-AUG-17 15.49.42.239283000  
1   COL3     value3   11-AUG-17 15.49.42.239283000  
1   COL4     value4   11-AUG-17 15.49.42.239283000  
1   COL5     value5   11-AUG-17 15.49.42.239283000  
1   COL6     12345    11-AUG-17 15.49.42.239283000  
2   COL1     value6   11-AUG-17 15.49.42.340387000  
2   COL2     value7   11-AUG-17 15.49.42.340387000  
2   COL3     value8   11-AUG-17 15.49.42.340387000  
2   COL4     value9   11-AUG-17 15.49.42.340387000  
2   COL5     value10  11-AUG-17 15.49.42.340387000  
2   COL6     23456    11-AUG-17 15.49.42.340387000  

(I set up dummy data with different values in the unpivoted columns, and just used systimestamp to get the timestamp column value).

When you run it dynamically it doesn't really do anything - it's parsed but not fully executed because you aren't executing immediate into anything.

My plan was to use this in a view

You can make your anonymous block generate the view dynamically, as a one-off event:

  sql_stmt := 'create or replace view your_view_name as
    select pk,
      varname,
      valuer,
      max(timestamp) over (partition by pk) as timestamp
      from (select pk, timestamp, ' || subquery || ' from test)
      unpivot(valuer for varname in (' || pivot_clause || '))';

  execute immediate sql_stmt;

and you can then just select from your_view_name.

Whenever a new column is added to the table (which is hopefully rare and under change control - but then you wouldn't really need to do this dynamically) you can just re-run the block to recreate the view.