# How To Use In Oracle PIVOT Clause

## October 16, 2019 | ## Oracle DBA |

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;

The next example shows how to use clause Pivot in Oracle and summarising the “N**umbers**” 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));

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

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

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

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;

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

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

**See Also:**

Oracle Select Home

## Leave a Reply