How to pivot a table in Oracle PLSQL?

Question

I have a table given as below:

KEY 1995    1996    1997    1998    1999    2000    2001    2002    2003
123 0       0       0       461     1188    2049    1056    377     295

And I want the data to be visible as given below.

KEY SEQ_NBR SEQ_YR  VALUE_SUM
123 1       1995    0
123 2       1996    0
123 3       1997    0
123 4       1998    461
123 5       1999    1188
123 6       2000    2049
123 7       2001    1056
123 8       2002    377
123 9       2003    295

I am using Oracle 12c. I tried using Pivot clause but couldn't create the query.

Can someone please help me?

Answer 1

For the fun of it: I am filling the new table from the old one with a PL/SQL loop and dynamic SQL. This is nothing we would regularly do, but why not do it for a one-time task?

begin
  for col in
  (
    select column_name
    from user_tab_cols
    where table_name = 'OLDTABLE' and regexp_like(column_name, '^[[:digit:]]{4}$')
  ) loop
    execute immediate
      'insert into newtable (key, year, value)
       select key, ' || col.column_name || ', "' || col.column_name || '" from oldtable';
  end loop;
  commit;
end;