Oracle PL/SQL: Pivoting a SQL Query within SQL

This blog post includes a rather simple script that will allow a developer to quickly pivot a single-table query so that rather than returning a single row will the data it returns multiple rows representing Column Name/Value combinations.


Let's assume we have a fairly simple table called PIVOTTEST this table, apart from displaying a distinct lack of imagination as far as naming goes, contains four columns ID (number), NAME (varchar2), DATE_CREATED (date) and DATE_LAST_UPDATED (date). It's created using the SQL:

create table PIVOTTEST
(
  ID                number,
  NAME              varchar2(60),
  DATE_CREATED      date,
  DATE_LAST_UPDATED date
);

In order to do our test let's put a few records into the table;

insert into pivottest(id, name, date_created, date_last_updated) values (1, 'ANDY', sysdate-200, sysdate - 5);
insert into pivottest(id, name, date_created, date_last_updated) values (2, 'BRETT', sysdate-190, sysdate - 4);
insert into pivottest(id, name, date_created, date_last_updated) values (3, 'COLIN', sysdate-180, sysdate - 3);
insert into pivottest(id, name, date_created, date_last_updated) values (4, 'IAN', sysdate-170, sysdate - 2);
insert into pivottest(id, name, date_created, date_last_updated) values (5, 'ADAM', sysdate-160, sysdate - 1);
commit;

If we do a SELECT * FROM PIVOTTEST WHERE ID = 1 we get a single record back:

 ID NAME DATE_CREATED DATE_LAST_UPDATED
 1 ANDY 10-MAY-2008 21-NOV-2008 

Yours dates will be different, and the format will be determined by your system settings but you get the point.

Assuming we'd prefer to have the results as multiple columns we would be aiming at something looking like:

 Column Name Column Value 
 ID 1 
 NAME ANDY 
 DATE_CREATED 10-MAY-2008 
 DATE_LAST_UPDATED 21-NOV-2008 

The easiest way to do this is to use multiple UNIONS and select each field we're interested in in turn:

select 1 ID, 'ID' Name, to_char(ID) Value from APPS.PIVOTTEST where ID = 1
union
select 2 ID, 'NAME' Name, NAME Value from APPS.PIVOTTEST where ID = 1
union
select 3 ID, 'DATE_CREATED' Name,to_char(DATE_CREATED) Value from APPS.PIVOTTEST where ID = 1
union
select 4 ID, 'DATE_LAST_UPDATED' Name, to_char(DATE_LAST_UPDATED) Value from APPS.PIVOTTEST where ID = 1

First thing; in order to allow the UNION to work the columns have to be of the same type. I've go for "character" just because it's the one practically every type has in common. In theory it will depend on the data you're working with but in practice you'll almost certainly want to use characters!

You'll notice that I've included the "WHERE ID =1" clause at the end to just give me the record I'm interested in and I've numbered the select statements so that when they are all joined together with the UNION the columns still come out in the order I'm expecting (if you remove the 1, 2, 3, 4 from the SELECT ... ID then the records come back in alphabetical column name order ... do you want that?).

Because the table details are held in Oracle you can actually do the same thing using a script:

-- Created on 25-Nov-2008 by APE06 
declare
  -- Local variables here
  cursor c_Columns is
    select atc.COLUMN_ID,
           atc.owner,
           atc.table_name,
           atc.COLUMN_NAME,
           atc.DATA_TYPE
      from all_tab_columns atc
     where atc.owner = 'APPS'
       and atc.TABLE_NAME = upper('PIVOTTEST')
     order by atc.COLUMN_ID;


  v_Where varchar2(255) := 'ID = 1';
begin
  for v_Column in c_Columns loop
    dbms_output.put_line('select ' || v_Column.column_id ||
                         ' ID, ''' || v_Column.column_name ||
                         ''' Name, nvl(' || 
                         case 
                         when v_Column.Data_Type = 'DATE' then 'to_char(' || v_Column.column_name || ',''DD-MON-YYYY'')' 
                         when v_Column.Data_Type = 'NUMBER' then 'to_char(' || v_Column.column_name || ')' 
                         else v_Column.column_name 
                         end ||
                         ', '''') Value from ' || v_Column.Owner || '.' ||
                         v_Column.Table_name || ' where ' || v_Where);
    dbms_output.put_line('union');
  end loop;
end;

You need to change the OWNER (from APPS), the TABLE_NAME (from PIVOTTEST), and your where clause condition to return a single row (from ID = 1) and then you're ready to go.

You should also watch out for the "union" that gets tacked on the end ... you'll need to delete that (I could have added a "select '','' from dual where 3=1" to get rid of it but ... well you can do that yourselves now can't you? (I'm also using PL/SQL Developer a a test window which makes copy/pasting very easy so I don't really need 100% accuracy).

This script will generate the SQL to query the table as a Column Name/ Value combination - it also does a few other "nice" things like specifying the format for the date and displaying when the column has a null value.

I hope this helps!