How to Efficiently Convert Rows to Columns in PL/SQL?

In Oracle PL/SQL, converting rows into columns is a common operation, especially useful for reporting, data analysis, and reformatting data for easy visualization. PL/SQL, or Procedural Language/Structured Query Language, is a powerful procedural extension to SQL, created by Oracle, that integrates procedural constructs such as loops, conditions, and exception handling with SQL’s power.

This article will guide you through the methods for converting rows to columns in Oracle, leveraging both the DECODE function and the PIVOT clause. By the end, you will understand how to efficiently reshape data for improved readability and analysis.

DECODE Function

The DECODE function in Oracle SQL is a conditional expression that compares an expression (expr) to a specified value (val). If the two match, it returns a specified result (res). It serves as a concise alternative to the if-else statement in procedural languages.

Syntax

DECODE(expr, val, res)

Parameters:

• expr: The expression to search the value in.
• val: The value to search in the expression.
• res: The value to return if the search is successful.

Example

The following query returns 'found' if the current year is 2024.

SELECT CURRENT_DATE, DECODE(EXTRACT(YEAR FROM CURRENT_DATE), 2024, 'found') 
FROM DUAL;

Output:

PIVOT Clause

The PIVOT clause is used to cross-tabulation clauses i.e. clauses modifying rows to become.

Syntax

SELECT * FROM
(
  SELECT col1, col2,..., colN
  FROM col1, col2,..., colN
  [WHERE condition]
)
PIVOT 
(
  agg_func(col)
  FOR col  IN ( expr1, expr2, ... exprN )
)
[ORDER BY expression [ ASC | DESC ]]; 

Parameters:

• condition: Optional filtering conditions for the data.
• agg_func: The aggregation function to use.
• col: The column whose values are to be used in the pivot clause.
• expr1, expr2, ... exprN: Expressions defining values for the columns used in the pivot.
• expression: An optional expression used to order the final output data.

We will look at an example in the following sections.

Methods for Converting Rows to Columns in Oracle PL/SQL

Efficiently converting rows to columns in PL/SQL is a common database task. In PL/SQL, there are two efficient ways to achieve row-to-column transformation.

The DECODE Function is an older but effective method while the PIVOT Clause is a more modern and efficient approach in Oracle SQL. Each method has its benefits and limitations, which we’ll cover in detail.

Setting up the Environment

Let us first set up some environment that can we use to understand the concepts in the following sections. The following query creates a table and inserts some records in it:

CREATE TABLE test(
    id INT,
    val1 VARCHAR(20),
    val2 VARCHAR(20)
);

INSERT INTO test VALUES (1, 'type1', 20);
INSERT INTO test VALUES (1, 'type2', 9);
INSERT INTO test VALUES (2, 'type3', 11);
INSERT INTO test VALUES (2, 'type1', 71);
INSERT INTO test VALUES (2, 'type2', 89);
INSERT INTO test VALUES (3, 'type3', 41);
INSERT INTO test VALUES (3, 'type1', 433);

The following is the initial data in the table:

Now that we have everything in place, we will look at two different methods to efficiently convert rows to columns in PL/SQL.

1. Converting Rows to Columns Using the DECODE Function

Earlier we saw what Decode function does. In the following query, we will make use of decode function to convert rows to columns. We group the data by the id column and then use the decode function to return the max of the val2 column for values 'type1', 'type2', and 'type3' in the val1 column.

SELECT id,
    MAX(DECODE(val1, 'type1', val2)) AS type1,
    MAX(DECODE(val1, 'type2', val2)) AS type2,
    MAX(DECODE(val1, 'type3', val2)) AS type3
  FROM test
GROUP BY id
ORDER BY id;

Output:

Explanation:

• The DECODE function returns the value from val2 when val1 matches 'type1', 'type2', or 'type3'.
• The MAX function is used to retrieve the maximum value for each type, which is especially useful when multiple entries for a type exist within the same id.

2. Converting Rows to Columns Using the PIVOT Clause

Earlier we saw what the Pivot clause is. We understood that it can be used to convert rows to columns. In the following query, we do the same. We convert all the rows which have values 'type1', 'type2', or 'type3' in the val1 column to separate columns. We make use of the MAX aggregation function.

SELECT * FROM test
PIVOT 
(
   MAX(val2)
   FOR val1 IN ('type1','type2','type3')
);

Output:

Explanation:

• The inner query selects the data, while the PIVOT clause converts values in val1 to column headers.
• MAX(val2) is applied to get the maximum val2 for each type within each id.

Comparing DECODE and PIVOT for Row-to-Column Transformation

Conclusion

In this article, we saw how we can efficiently convert rows to columns in PL/SQL. We started by looking at several functions like DECODE and PIVOT. We understood what they do and saw examples of them. We later saw several methods to find the required values. First, we saw how to use the decode function to convert the row to the column using a hack and then saw the proper use of the PIVOT clause to easily convert rows to columns.