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?

You don't need Oracle's programming language PL/SQL for this. A mere SQL query will suffice. — Thorsten Kettner, Dec 09 '19 at 09:01

score 2 · Answer 1 · answered Dec 09 '19 at 08:58

2

You can can use union to unpivot. This is a standard technique that works across most RDBMS (unlike vendor-specific pivot/unpivot methods).

select key, 1 seq_nbr, '1995' seq_yr, "1995" value_sum
union all select key, 2 seq_nbr, '1996' seq_yr, "1996" value_sum
union all select key, 3 seq_nbr, '1997' seq_yr, "1997" value_sum
union all select key, 4 seq_nbr, '1998' seq_yr, "1998" value_sum
union all select key, 5 seq_nbr, '1999' seq_yr, "1999" value_sum
union all select key, 6 seq_nbr, '2000' seq_yr, "2000" value_sum
union all select key, 7 seq_nbr, '2001' seq_yr, "2001" value_sum
union all select key, 8 seq_nbr, '2002' seq_yr, "2002" value_sum
union all select key, 9 seq_nbr, '2003' seq_yr, "2003" value_sum

answered Dec 09 '19 at 08:58

GMB

216,147
25
84
135

That was my first thought as well. However, there are 55 such columns in the table. Creating a union statement with 55 sub-statements is quite cumbersome. I was wondering whether there is something shorter that I can use for creating the output table listed in the question? – Yash Sharma Dec 09 '19 at 09:03
@Abhinav Dhiman: But who cares? That's just some copy and paste, and I suppose a one-time task at that, because you are probably about to replace the bad old table with your better one? (You don't need the `SEQ_NBR` by the way, as it's just `SEQ_YR` minus 1994, isn't it?) – Thorsten Kettner Dec 09 '19 at 09:06
@ThorstenKettner Yeah. That's true. This question was for my own query. Just wanted to know whether there is some other way it can be done. :) – Yash Sharma Dec 09 '19 at 09:10
@Abhinav Dhiman: As that's just for a migration, we can of course write a small script. Much slower than the mere query of course, but as it's a one-time thing, that shouldn't matter much I've posted an answer with a small PL/SQL block doing the task. – Thorsten Kettner Dec 09 '19 at 09:33

score 2 · Accepted Answer · answered Dec 09 '19 at 09:30

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;

For a one-time task this is a nice soution as well. – GMB Dec 09 '19 at 12:21 — GMB, Dec 09 '19 at 12:21

How to pivot a table in Oracle PLSQL?

2 Answers2

Linked