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.
