DEV Community

Cover image for Transpose rows to columns in Oracle SQL using Oracle PIVOT clause
Sharan Kumar Paratala Rajagopal
Sharan Kumar Paratala Rajagopal

Posted on • Updated on

Transpose rows to columns in Oracle SQL using Oracle PIVOT clause

In Oracle 11g PIVOT clause helps to convert the data from row to column. Below are the examples to convert two column table and three column table result sets to cross tab format.

This is very helpful for reporting and also queries where data has to be viewed as cross table. This is similar to excel PIVOT functionality.

Two column PIVOT:

INPUT:

Alt Text

SQL QUERY:

select * from (
  select t.cstore_number, t.attr_Value,
         row_number() over (partition by cstore_number order by attr_Value) rn from STORE_ATTR t)
pivot (
  min(attr_Value)
  for (rn) in (1 as DEALERCODE1, 2 as DEALERCODE2, 3 as DEALERCODE3, 4 as DEALERCODE4, 5 as DEALERCODE5)
);

Output:

Alt Text

Three column pivot:

Input:

Alt Text

SQL query:

Alt Text

OUTPUT:

Alt Text

Summary
Now you should be able to convert or transpose rows to columns into crosstab format by using oracle PIVOT clause.

Top comments (0)