How Efficiently Convert Rows to Columns in PL/SQL?

PL/SQL, or Procedural Language/Structured Query Language, is an extension of SQL used for writing procedural code within Oracle databases. It is a proprietary procedural extension of SQL developed by Oracle Corporation specifically for Oracle Database.

It combines the power of SQL with procedural constructs like loops, conditions, and exception handling. It is a blocked programming language, programming units can be named or unnamed blocks. Unnamed blocks are never stored in the database.

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:

Output of decode query

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.

How Efficiently Convert Rows to Columns in PL/SQL

Efficiently converting rows to columns in PL/SQL is a common database task. Whether using the PIVOT clause or DECODE function statements, this process transforms data structures for improved analysis and reporting.

• Using Decode Function
• Using Pivot Clause

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:

Initial test data

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

1. Using 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:

output of using the decode function

Explanation: In the above query, we made use of three DECODE function calls to find the maximum value for each type1, type2, and type3.

2. Using 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:

output of using the pivot function

Explanation: The output of the query will be a table where each row represents a unique combination of ‘type1‘, ‘type2‘, ‘type3‘, and the values in the columns are the maximum values of val2 for each combination.

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