Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 9, 2013

How to Pivot Data in Oracle 11g

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM