How to Pivot Data in Oracle 11g

Occasionally it may be necessary to ‘flip’ a table on its side, so to speak, meaning to convert rows to columns in order to display data in a more understandable format. Depending on the release of Oracle there are several techniques that can be used to accomplish this task. Most of these, however, require a knowledge of the number of rows, and key values that need to be converted. With Oracle 11g it’s actually possible to define a dynamic solution to this problem, using the pivot operator. The pivot operator is described here, as part of the SELECT syntax: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF01702

Basically described, PIVOT operates on the base query (the SELECT statement) and applies an aggregate supplied by the user to pivot the data from rows to columns that report the aggregated data. Its use will be illustrated in the following example.

To begin, a table is created and populated that provides the data that needs to be converted:

SQL> create table name_qty(
  2          name            varchar2(20),
  3          quantity        number
  4  );

Table created.

SQL>
SQL> insert all
  2  into name_qty
  3  values('COL_A', 5000)
  4  into name_qty
  5  values('COL_B', 3000)
  6  into name_qty
  7  values('COL_C', 3000)
  8  select * From dual;

3 rows created.

SQL> select name, quantity
  2  from name_qty;

NAME                   QUANTITY
-------------------- ----------
COL_A                      5000
COL_B                      3000
COL_C                      3000

SQL>

Three column names and three associated values. The goal is to associate the proper value with the proper column name, so the next step in this process is to generate a list of values comprised of the key data, the data that will name the new columns in the output. Using the WM_CONCAT function this is a fairly easy task:

SQL>
SQL> column namelist new_value n_list noprint
SQL>
SQL> select wm_concat(''''||name||'''') namelist
  2  from name_qty
  3  connect by nocycle name = prior name
  4  group by level;




SQL>

To keep the output of the entire process uncluttered the resulting column from the query shown above is not printed. [For reference purposes the output at this stage of the solution would be (‘COL_A’,’COL_B’,’COL_C’). Note also the use of the NEW_VALUE SQL*Plus function to associate the column output with a variable name that can be used later in the script. That step allows the use of the generated string in the resulting pivot query.] This is done since both the previous query and the query shown below will be placed in a single script to generate the columnar output. The script is generating a list to be used by the pivot operator to generate column names and associate the correct value with the proper column. Now it’s time to write the workhorse of this solution, the pivot query itself, which requires an aggregate on which to operate. SUM() is chosen in this example as the resulting value is computed by grouping on the NAME column, which will not change the value to be displayed:

SQL> select *
  2  from
  3  (select name, quantity
  4   from name_qty nq
  5  )
  6  pivot
  7  (
  8          sum(quantity)
  9          for name in (&n_list)
 10  );

   'COL_A'    'COL_C'    'COL_B'
---------- ---------- ----------
      5000       3000       3000

SQL> 

The effort is successful as correct values are associated with the proper column names. What makes this solution so convenient is the fact that rows can be added to the source table and the pivoted output will not be missing any columns. Adding data to the table and running the script again generates the following output:

SQL> insert all
  2  into name_qty
  3  values('COL_D', 5000)
  4  into name_qty
  5  values('COL_E', 3000)
  6  into name_qty
  7  values('COL_F', 3000)
  8  select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select wm_concat(''''||name||'''') namelist
  2  from name_qty
  3  connect by nocycle name = prior name
  4  group by level;




SQL>
SQL> select *
  2  from
  3  (select name, quantity
  4   from name_qty nq
  5  )
  6  pivot
  7  (
  8          sum(quantity)
  9          for name in (&n_list)
 10  );

   'COL_A'    'COL_F'    'COL_E'    'COL_D'    'COL_C'    'COL_B'
---------- ---------- ---------- ---------- ---------- ----------
      5000       3000       3000       5000       3000       3000

SQL>

The script didn’t change but the output now correctly reflects the addition of three rows to the source table, and, as a result, three new columns in the pivoted output. This format also makes the data easier to read and understand.

The PIVOT operator can be a powerful tool for turning data into information, especially when looking at a data in a row format can be tedious or confusing.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles