How To Use In Oracle PIVOT Clause

How To Use In Oracle PIVOT Clause

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. The Oracle Pivot clause in Oracle Select statement allows to write matrix type of queries and it rotates rows into columns, aggregating data in the process of the rotation. The result of a pivot clause includes more columns and a fewer rows than the starting data set. The most 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 next Oracle select query will be used as a “source” for the following Oracle Pivot operator examples and you can see on the picture of output what does it return without applying any Pivot clause restrictions. The Oracle MOD (module) function is used to create repeating number values.

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

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

The next example shows how to use clause Pivot in Oracle and summarising 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 named by ID value. You should notice that ID value 6 has no value and the reason is in the query above that has no 6th ID. 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));

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

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

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

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

If you did not get the error above then you will be able to continue with our examples. The second Oracle DBA example with the Pivot Oracle will use Oracle 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));

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

The next 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 ID 6th.

 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));

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

Now lets see how will change the output of the SQL query result with Oracle COUNT(*) function. The Pivot Oracle clause has 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));

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

The following Oracle DBA examples will need more complicated “base” output to bring out in a better way the example and to show how exactly acts Oracle Pivot operator. We added an extra Oracle Mod function to the “Numbers” column to get more repeating values.

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

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

This Oracle SQL query is exactly the same as in the last Oracle Pivot example with only one exception the “source” table has changed and it affects the output. We did include the “source” Oracle Select so you could compare the source and result between two table and understand better Oracle Pivot clause.

 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));

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

Our Oracle DBA example shows how to get the greatest ID value grouped per chosen IDs and Oracle Pivot operator takes 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));

dba oracle dba database management system oracle performance tuning oracle pivot oracle pivot in oracle stock market basics linux server linux ORA-00933: SQL command not properly ended

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*