DEV Community

loading...
Cover image for Transpose rows to columns in Oracle SQL using Oracle PIVOT clause

Transpose rows to columns in Oracle SQL using Oracle PIVOT clause

prsharankumar profile image Sharan Kumar Paratala Rajagopal Updated on ・1 min read

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.

Discussion (0)

Forem Open with the Forem app