How to use Decode Function In SQL
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: In the above query, we made use of three DECODE function calls to find the maximum value for each type1, type2, and type3.
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.