Oracle Tips and Tricks — David Fitzjarrell

June 17, 2015

Let Me Say That Another Way …

Filed under: General — dfitzjarrell @ 08:50

"I never knew words could be so confusing," Milo said to Tock as he bent down to scratch the dog's ear.
"Only when you use a lot to say a little," answered Tock.  Milo thought this was quite the wisest thing he'd heard all day.
-- Norton Juster, The Phantom Tollbooth

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 which 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 which 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. Another way to do this is with the xmlagg() function:


SQL> SELECT *
  2  FROM (SELECT rtrim(xmlagg(xmlelement(e,name||',') ORDER BY name).EXTRACT('//text()'),',')  names
  3                 ,quantity
  4           FROM   name_qty
  5           GROUP BY   name,quantity
  6          )
  7          Pivot   (sum(quantity)
  8          for names in (&n_list)
  9                  );

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

SQL>

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> SELECT *
  2  FROM (SELECT rtrim(xmlagg(xmlelement(e,name||',') ORDER BY name).EXTRACT('//text()'),',')  names
  3                 ,quantity
  4           FROM   name_qty
  5           GROUP BY   name,quantity
  6          )
  7          Pivot   (sum(quantity)
  8          for names in (&n_list)
  9                  );

   'COL_A'    'COL_B'    'COL_C'    'COL_D'    'COL_E'    'COL_F'
---------- ---------- ---------- ---------- ---------- ----------
      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.

It’s usually best to say more with less.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: