How to convert rows into columns in Oracle

In this Oracle tutorial, we will learn how to convert rows into columns in oracle databases 19c & 21c. Also, we will cover these topics.

• Oracle how to convert rows into columns
• Oracle how to transpose rows into columns
• Oracle turn rows into columns
• Oracle function to convert rows into columns
• How to convert rows into columns in oracle without pivot
• How to convert rows into columns in oracle using decode
• How to convert rows into columns in oracle with example

Oracle how to convert rows into columns

Oracle provides a Pivot clause using which we can convert rows into columns. This clause helps in displaying huge data nicely on the quickly understandable screen.

The Pivot clause arranges data in a crosstab view that becomes easy to read and understand. You can choose the row of choice that you want to make a column.

Below is the syntax of the Pivot clause in the oracle database:

SELECT * FROM
(
  SELECT col1, col2
  FROM tables_name
  WHERE conditions
)
PIVOT 
(
  aggregate_function(col2)
  FOR col2
  IN ( newcol1, newcol2, newcol3 ...newcol_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

• Aggregate_function can be count(), max(), min(), sum(), etc.
• newcol1, newcol2 … newcol_n are the columns names for the table
• order by either ascending or descending order.

The Pivot clause uses aggregate functions on the row and then using loop converts each row value to a column. For loop checks for the presence of row in the provided tuple. If the value matches then a column is created.

Below example shows, doors available in the different models of car:

SELECT * FROM (
SELECT MODEL, DOORS 
FROM CAR)
PIVOT
( COUNT(DOORS)
FOR DOORS IN (6 ,5, 3, 4, 2));

Output:

In the below output, we have converted rows into columns in the oracle database using the pivot clause. The output displays the car model and the number of doors available. Car model wagons have 5 doors, Mercedes has 6 doors, and so on.

Read: Oracle check database status

Oracle how to transpose rows into columns

Transpose here means exchanging the position of rows into columns in the oracle database. In oracle, there are two ways of transposing rows into columns:

• Oracle Pivot Clause
• Oracle Decode Clause

Oracle Pivot Clause is introduced in oracle version 11g. In the Pivot clause, the aggregate function is applied to the column name to find one value for that column. An aggregate function can be Count, Max, Min, Sum, etc. the loop is run the number of times the aggregate value result is.

The loop value is compared with the values present in the tuple. If the value matches then the count is incremented otherwise remains zero.

The oracle decode function does the same thing that the pivot table does but it consumes more lines of code. Instead of a loop, decide function performs a comparison on each line.

In the below script, we have demonstrated both the ways Pivot function and Decode function in oracle database 19c and 21c.

-- CREATE TABLE 
CREATE TABLE USA_HOSPITALS(
    ID INTEGER,
    NAME VARCHAR(200),
    CITY VARCHAR(100),
    ZIP INTEGER
);


-- INSERT DATA
INSERT INTO USA_HOSPITALS
VALUES(2001, 'WOODS AT PARKSIDE,THE', 'COLUMBUS', 43230);

INSERT INTO USA_HOSPITALS
VALUES(2002, 'TRINITY HOSPITALS', 'MINOT', 58701);

INSERT INTO USA_HOSPITALS
VALUES(2003, 'SITKA COMMUNITY HOSPITAL', 'SITKA', 99835);

INSERT INTO USA_HOSPITALS
VALUES(2004, 'MT EDGECUMBE HOSPITAL - SEARHC', 'SITKA', 99835);

INSERT INTO USA_HOSPITALS
VALUES(2005, 'ELMENDORF AFB HOSPITAL', 'ANCHORAGE', 99506);

INSERT INTO USA_HOSPITALS
VALUES(2006, 'PROVIDENCE ALASKA MEDICAL CENTER', 'ANCHORAGE', 99508);

INSERT INTO USA_HOSPITALS
VALUES(2007, 'PROVIDENCE ALASKA MEDICAL CENTER', 'ANCHORAGE', 99508);

-- VIEW DATA

SELECT * FROM USA_HOSPITALS;

-- PIVOT FUNCTION
SELECT * FROM (
  SELECT NAME,CITY
  FROM USA_HOSPITALS)
PIVOT(COUNT(CITY) 
  FOR (CITY) IN ('MINOT', 'SITKA', 'ANCHORAGE'));

-- DECODE FUNCTION
SELECT
    Name,
    COUNT(DECODE(CITY, 'MINOT', CITY)) AS "MINOT",
    COUNT(DECODE(CITY, 'SITKA', CITY)) AS "SITKA",
    COUNT(DECODE(CITY, 'ANCHORAGE', CITY)) AS "ANCHORAGE" 
   FROM USA_HOSPITALS

GROUP BY Name
ORDER BY Name;

In the below output, the Name of the hospital is displayed with the city names as the column.

Read: Connect to oracle database

Oracle turn rows into columns

Oracle provides a pivot clause using which oracle rows can be turned into columns in oracle database 19c & 21c.

In the previous sections, we have discussed the pivot clause. so in this section, we will see another example to demonstrate the pivot clause in oracle database 19c & 21c.

In the below example, we have turned the rows of the Brand column into new columns. The earlier Brand column has values Chevrolet, GMC, Ford, and Dodge but now using the Pivot function we have converted them into separate columns.

SELECT * FROM (
  SELECT  BRAND, YEAR, PRICE
  FROM CARS_IN_USA)
PIVOT(COUNT(BRAND) 
  FOR (BRAND) IN ('chevrolet', 'GMC', 'FORD', 'DODGE'));

The below output shows, in 2017 GMC was introduced for $13340.00, followed by FORD at $5250 and so on.

Read: Oracle get database name

Oracle function to convert rows into columns

Oracle provides a pivot function using which rows can be converted into columns in oracle database 19c & 21c. Pivot function or clause was introduced in 11g before that oracle decode function was used.

The Pivot clause converts rows into columns using the aggregate function. Aggregate functions like SUM(), COUNT(), MAX(), MIN(), etc.

Another method to convert rows into columns is by using decode but that function is not efficient as the pivot is because it involves repetition of statements whereas the oracle Pivot function converts rows into columns in a few lines only.

We have explained both the ways in the above section – Oracle how to transpose rows into columns

How to convert rows into columns in oracle without pivot

In oracle version 11g, the pivot function was introduced by oracle which allows developers to view the data in a crosstab.

The pivot function contributes to the presentation of large data. One can display huge data in such a way that is easily understandable by the observers.

Before the release of the pivot function, developers were using the Decode function. This function allows creating a crosstab comparison of data that helps in good presentation and easy to understand presentation of huge data.

Below is the syntax of Decode:

DECODE( 
    expression , 
    search, 
    result 
    [, search , result]... [, default]
 )

In the upcoming section, we have shared an example for converting rows into columns with decode in oracle database 19c and 21c.

Read: Oracle Add Row Number

How to convert rows into columns in oracle using decode

Decode is another function used to convert rows into columns in oracle databases 19c and 21c. It compares expression or provided column to each search value one by one.

It was widely used before the introduction of the pivot function and can be called a rival of the pivot function. Thought it was a little longer but does the same thing done by the pivot function.

The decode function is more like the if-else statement. It compares the statement, if the statement matches then it displayed the 1 otherwise displayed 0.

Script:

The below script will create a table of Airports in the United States of America then inserts a few records. In the end, performs the Decode function to display available flights based on the USA cities.

-- CREATE TABLE 
CREATE TABLE AIRPORTS_IN_USA (
  ORIGIN_CITY VARCHAR2(100),
  DESITNATION_CITY VARCHAR2(100),
  FLIGHTS_AVAILABLE INTEGER);

-- INSERT DATA
INSERT INTO AIRPORTS_IN_USA 
VALUES('Eugene, OR', 'Bend, OR' , 4);

INSERT INTO AIRPORTS_IN_USA 
VALUES('Medford, OR', 'Bend, OR' , 1);

INSERT INTO AIRPORTS_IN_USA 
VALUES('Seattle, WA', 'Bend, OR' , 23);

INSERT INTO AIRPORTS_IN_USA 
VALUES('Eugene, OR', 'Bend, OR' , 4);

INSERT INTO AIRPORTS_IN_USA 
VALUES('Manhattan, KS', 'Ames, IA' , 1);

INSERT INTO AIRPORTS_IN_USA
VALUES('Portland, OR', 'Bend, OR', 154)

-- COMMIT CHANGES
COMMIT;

-- VIEW DATA 
SELECT * FROM AIRPORTS_IN_USA;

-- USING DECODE 
SELECT
    ORIGIN_CITY,
    COUNT(DECODE(FLIGHTS_AVAILABLE, 4, FLIGHTS_AVAILABLE)) AS "4 flights",
    COUNT(DECODE(FLIGHTS_AVAILABLE, 1, FLIGHTS_AVAILABLE)) AS "1 flight",
    COUNT(DECODE(FLIGHTS_AVAILABLE, 23, FLIGHTS_AVAILABLE)) AS "23 flights",
    COUNT(DECODE(FLIGHTS_AVAILABLE, 154, FLIGHTS_AVAILABLE)) AS "154 flights" 
   FROM AIRPORTS_IN_USA

GROUP BY ORIGIN_CITY
ORDER BY ORIGIN_CITY;

Output:

In the below output, the number of flights available from the origin city is displayed. 4 flights are available from Eugene in 2-time frames. so in a day total of 8 flights are available. 1 flight is available from Manhattan and Medford.

Read: Oracle Add a Column to Table

How to convert rows into columns in oracle with example

We have covered various examples in the above sections, but this time we are going to use a CSV file that has 100 records. More records in the table allow implementing the Pivot function in better ways.

Moving forward let’s see the overview of the dataset whose rows will be converted to columns in oracle database 19c and 21c.

Overview of the dataset:

We have downloaded the CSV dataset of Coffee And Code from the Kaggle website. Download the file and import data by writing clicking on the table on the SQL developer tool.
Read Blog – Create Table Using CSV File

Script:

In the below script, the pivot clause is applied on the CoffeeType. The statement (COUNT(COFFEETYPE) counts total entries of coffee types in the table. Now, this information is used to run the total number of times for loop will run. Each time loop runs it checks if the value in the present row in the given tuple with coffee types. If yes then the column is created and the count has been maintained otherwise output remains 0.

SELECT * 
FROM(
    SELECT GENDER, COFFEETYPE
    FROM CoffeeAndcode)
PIVOT(COUNT(COFFEETYPE)
    FOR(COFFEETYPE) IN (
    'Nescafe', 'Turkish', 'Cappuccino', 
    'American Coffee', 'Espresso (Short Black)'));

Output:

In the below output, new columns with the coffee names are created. The output shows the type of coffee preferred by male and female coders. Nescafe is preferred by 18 males and 14 females.

You may also like to read the following tutorials.

• Oracle Create User
• Oracle to_char Date Format
• Oracle Add Foreign Key
• Number Datatype in Oracle
• Connect Excel to Oracle
• Delete Duplicate Rows in Oracle

In this tutorial, we have learned how to convert rows into columns in oracle databases 19c & 21c. Also, we have covered these topics.

• Oracle how to convert rows into columns
• Oracle how to transpose rows into columns
• Oracle turn rows into columns
• Oracle function to convert rows into columns
• How to convert rows into columns in oracle without pivot
• How to convert rows into columns in oracle using decode
• How to convert rows into columns in oracle with example