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:

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:

Three column pivot:

Input:

SQL query:

OUTPUT:

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