Online Tech Support

This page is based on examples to be easier to follow. The Oracle Pivot operator allows you to write matrix type of queries that rotate rows into columns, aggregating data in the process of the rotation. The result of a pivot operation includes more columns and fewer rows than the starting data set. The basic Oracle Pivot operator syntax is:

SELECT <columns>
  FROM <your_query_name>
PIVOT (<aggregating expression> 
       FOR <column_x_name> IN (<column_x_value1>,<column_x_value2>,...));

The following Oracle select will be used as a “source” query for the following Oracle Pivot operator examples and you can see on the next picture the output it returns without applying any restrictions. The Oracle MOD function is used to create repeating numbers.

 SELECT MOD(ROWNUM,5) AS id, ROWNUM * 2 AS numbers
   FROM dual
CONNECT BY ROWNUM < 10;

The next example shows how to use operator Pivot in Oracle and summaries the “Numbers” column for “ID” values 1,2,4 and 6. The pivot Oracle operator set all grouped values on the same line per ID into different columns. You should notice that ID value 6 has no value and the reason is in the query above where the 6 ID is missing. But since ID 6 is declared as possible value then Oracle Pivot creates an empty column for possible values in future.

 SELECT *
   FROM
    (SELECT MOD(ROWNUM,5) AS id, ROWNUM * 2 AS numbers
       FROM dual
        CONNECT BY ROWNUM < 10
    ) pivot ( SUM(numbers) FOR id IN (1,2,4,6));

Important To Know:


While you executed the last Oracle Select statement with using Oracle Pivot and instead of seeing the same result as in the example above you got the following error:

ORA-00933: SQL command not properly ended

Then your Oracle database does NOT support Oracle Pivot operator. The Pivot in Oracle has been supported since Oracle 11g version and unfortunately you are not able to use this select statements.

The second online tech support computer technician example with the Pivot Oracle will use MIN function instead of SUM and rest of the select has remind same as in the last example. Now the SQL output values are smaller because of the MIN function.

 SELECT *
   FROM
    (SELECT MOD(ROWNUM,5) AS id, ROWNUM * 2 AS numbers
       FROM dual
        CONNECT BY ROWNUM < 10
    ) pivot ( MIN(numbers) FOR id IN (1,2,4,6));

This example shows the same Oracle select as the last ones with Oracle Count function applied to the “Numbers” column. The select output shows counted lines for every declared “ID” values and there are two lines for 1,2 and 4 and zero lines for the 6th ID.

 SELECT *
   FROM
    (SELECT MOD(ROWNUM,5) AS id, ROWNUM * 2 AS numbers
       FROM dual
        CONNECT BY ROWNUM < 10
    ) pivot ( COUNT(numbers) FOR id IN (1,2,4,6));

Now lets see how looks the SQL query result with COUNT(*) function. The Pivot created a line per every value in the “Numbers” column and filled them with counted amount per “Numbers”. You should note that the counting function is looking for ID values 1,2,3 and 6 and ignores the rest as we have not declared them.

 SELECT *
   FROM
    (SELECT MOD(ROWNUM,5) AS id, ROWNUM * 2 AS numbers
       FROM dual
        CONNECT BY ROWNUM < 10
    ) pivot ( COUNT(*) FOR id IN (1,2,4,6));

The following examples will need more complicated base output to bring out better the example meaning and how exactly acts Oracle Pivot operator. We added another Oracle Mod function to the “Numbers” column as well to get more repeating values.

 SELECT MOD (ROWNUM,5) AS id, MOD(ROWNUM * 2,7) AS numbers
   FROM dual
    CONNECT BY ROWNUM < 10;

This SQL query is exactly the same as in the last Pivot Oracle example with only one exception the base table has changed and it affects the output. We included this Oracle select so you could compare the source and result between two table and understand better Oracle Pivot operator.

 SELECT *
   FROM
    (SELECT MOD (ROWNUM,5) AS id,MOD (ROWNUM * 2,7) AS numbers
       FROM dual
        CONNECT BY ROWNUM < 10
    ) pivot (COUNT (*) FOR id IN (1,2,4,6));

The next Pivot example shows how to select greatest ID value grouped per select IDs and Oracle Pivot operator select the “Number” values as the second selection value and builds up the greatest ID value on that.

 SELECT *
   FROM
    (SELECT MOD (ROWNUM,5) AS id,MOD (ROWNUM * 2,7) AS numbers
       FROM dual
        CONNECT BY ROWNUM < 10
    ) pivot (MAX(ID) FOR id IN (1,2,4));

See Also:
Home Oracle Select