Creating Pivot Tables

Pivot tables are incredibly useful when writing certain types of SQL queries, because they let you generate rows that do not otherwise exist. Here's a simple example that uses a pivot table to generate a report showing my remaining 2004 article deadlines:

SELECT ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), x)
FROM pivot
WHERE x >= EXTRACT(MONTH FROM SYSDATE)
  AND x < 12
ORDER BY x;

ADD_MONTH
---------
01-FEB-04
01-MAR-04
01-APR-04
01-MAY-04
01-JUN-04
01-JUL-04
01-AUG-04
01-SEP-04
01-OCT-04
01-NOV-04
01-DEC-04

If you're not familiar with pivot tables and how they can be used, then please read my September 2002 Oracle Magazine article on the topic:

[Article no longer available on Oracle's website]

In this article, I want to focus on how you create the pivot table to begin with. My article back in 2002 showed the following solution, which uses a rather clever SELECT statement that I learned from a Microsoft SQL Server programmer:

CREATE TABLE pivot (
   x NUMBER
   );

INSERT INTO PIVOT
SELECT huns.x+tens.x+ones.x FROM
  (SELECT 0 x FROM dual
   UNION SELECT 1 FROM dual
   UNION SELECT 2 FROM dual
   UNION SELECT 3 FROM dual
   UNION SELECT 4 FROM dual
   UNION SELECT 5 FROM dual
   UNION SELECT 6 FROM dual
   UNION SELECT 7 FROM dual
   UNION SELECT 8 FROM dual
   UNION SELECT 9 FROM dual) ones,
  (SELECT 0 x FROM dual
   UNION SELECT 10 FROM dual
   UNION SELECT 20 FROM dual
   UNION SELECT 30 FROM dual
   UNION SELECT 40 FROM dual
   UNION SELECT 50 FROM dual
   UNION SELECT 60 FROM dual
   UNION SELECT 70 FROM dual
   UNION SELECT 80 FROM dual
   UNION SELECT 90 FROM dual) tens,
  (SELECT 0 x FROM dual
   UNION SELECT 100 FROM dual
   UNION SELECT 200 FROM dual
   UNION SELECT 300 FROM dual
   UNION SELECT 400 FROM dual
   UNION SELECT 500 FROM dual
   UNION SELECT 600 FROM dual
   UNION SELECT 700 FROM dual
   UNION SELECT 800 FROM dual
   UNION SELECT 900 FROM dual) huns;

Much to my surprise, as soon as the September/October 2002 issue of Oracle Magazine hit my postal mailbox my email inbox began filling up with reader email, and just about every one of those readers took me to task over my method for creating the pivot table. 

Note: There is a CONNECT BY approach that is well-known on the web, but I have never circled back to add it to this article. Just search for "CONNECT BY row generated", and you are bound to find the technique.

Almost everyone, it seemed, had a solution that required much less typing than mine. Will Hitchcock proposed a PL/SQL approach for generating the 1000 rows that I required:

declare
   i number := 1;
begin
  for i in 0..999 loop
     insert into pivot values (i);
  end loop;
end;
/

Felipe Alzate weighed in next with an approach creating a work table of 10 rows, and then referencing that table multiple times in a second query to create the final, pivot table. Here's the work table with 10 rows:

CREATE TABLE pivot_from0to9 AS
SELECT ones.y AS x
FROM (SELECT 0 y FROM dual UNION SELECT 1 FROM dual UNION
      SELECT 2 FROM dual UNION SELECT 3 FROM dual UNION
      SELECT 4 FROM dual UNION SELECT 5 FROM dual UNION
      SELECT 6 FROM dual UNION SELECT 7 FROM dual UNION
      SELECT 8 FROM dual UNION SELECT 9 FROM dual) ones;

And here's the statement using that work table together with Cartesian products to generate the 1000-row pivot table:

CREATE TABLE pivot AS
SELECT p0.x*(1) + p1.x*(10) + p2.x*(100) x
FROM pivot_from0to9 p0, pivot_from0to9 p1,
     pivot_from0to9 p2;

Francisco Moreno sent a most interesting solution. Begin by inserting just two rows:

INSERT INTO Pivot VALUES(999);
INSERT INTO Pivot VALUES(0);

Then generate nine Cartesian products from ten references to the table:

INSERT INTO Pivot
SELECT rownum
FROM Pivot,Pivot,Pivot,Pivot,Pivot,Pivot,Pivot,Pivot,Pivot,Pivot
WHERE rownum < 999;

This works, because 2 raised to the 10th power works out to 1024, giving just a bit more than 1000 rows. The WHERE clause restricts the results to just the additional 998 rows needed to bring the table up to 1000.

Surprisingly, no one suggested that I leverage one of Oracle's data dictionary views to create and populate my table:

CREATE TABLE pivot AS
SELECT ROWNUM-1 x
FROM all_objects
WHERE ROWNUM <= 1000;

This is my least-favorite solution. It's probably a safe bet that ALL_OBJECTS will return 1000 rows, but you never know for sure, and it bothers me to depend on a row-source that I don't control. I'm rather glad no one suggested this solution.

All of this brings me to the reason I'm writing this article today. Last November, more than a year after my pivot table article appeared in print, I received yet another solution, and it's interesting because it involves a SQL clause I'd bet most of us don't often think about: the WITH clause. This solution comes from Vladimir Przyjalkowski, an Oracle technologist based in Moscow. Here it is:

CREATE TABLE pivot AS
WITH ones AS
   (SELECT 0 x FROM dual
    UNION SELECT 1 FROM dual
    UNION SELECT 2 FROM dual
    UNION SELECT 3 FROM dual
    UNION SELECT 4 FROM dual
    UNION SELECT 5 FROM dual
    UNION SELECT 6 FROM dual
    UNION SELECT 7 FROM dual
    UNION SELECT 8 FROM dual
    UNION SELECT 9 FROM dual)
    SELECT 100*o100.x + 10*o10.x + o1.x x
    FROM ones o1, ones o10, ones o100;

Vladimir's solution struck me with particular force, I think, because I'd only just finished researching the WITH clause in conjunction with a series of articles on recursive queries, the final of which was an article showing how to use the ANSI-standard WITH clause to write such queries.

So there you have it! More ways to create a pivot table than you can shake a stick at. I'd like to leave you with just one more solution, my personal favorite, which is to dispense with the table entirely and use a table function instead:

CREATE OR REPLACE TYPE pivot_row AS OBJECT (
   x NUMBER
);
/

CREATE OR REPLACE TYPE pivot_table
   AS TABLE OF pivot_row;
/

CREATE OR REPLACE PACKAGE pivot_package AS
FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table
   PARALLEL_ENABLE PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY pivot_package AS
   FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table PARALLEL_ENABLE PIPELINED IS
         outrow pivot_row := pivot_row (0);
      BEGIN
         FOR x IN 0..num_rows-1 LOOP
            outrow.x := x;
            PIPE ROW(outrow);

         END LOOP;
         RETURN;
      END;
END;
/

This table function approach works only in Oracle9i Database and higher. I like it, because you can use the table function to generate any number of rows you like. For example, to restate the query shown at the beginning of this article:

SELECT ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), x)
FROM TABLE(pivot_package.pivot(12))
WHERE x >= EXTRACT(MONTH FROM SYSDATE)
  AND x < 12
ORDER BY x;

Using a table function frees you from worry over whether you've created enough pivot table rows to begin with, to cover all your current and future needs. Instead, you get a virtual pivot table that generates any number of needed rows on-the-fly.

I hope you've enjoyed this little excursion through the land of pivot table creation. I'd like to end by thanking Vladimir, Will, Felipe, and Francisco for sharing their solutions. I hope you enjoy reading their solutions as much as I did.