Using decode elegant row to column transformation in Oracle

Time:2020-6-18

Using in OracledecodeElegant row to column

Recently, I finally met the application scenario of row to column conversion in my work. I took notes for myself to view and help people in need.

In other relational databases, it is recommended to usecase when then endThis kind of bloated and complicated writing method has recently seen the unique functions in Oracle DatabasedecodeIt’s very elegant. Let’s introduce it briefly.

decodefunction

definition:

Decode (condition, value 1, return value 1, value 2, return value 2,… Value n, return value n, default value)

In the code, it can be understood as:

If condition = = value 1:
    Return value 1
Elif condition = = value 2:
    Return value 2
Elif condition = = value n:
    Return value n
else:
    Default

In the relational database, it can be understood as:

Case condition
    When value 1 then return value 1
    When Value2 then return Value2
    When value n then return value n
    Else default 
End as column

Example:

TYPE NAME CLASS NUM_FLAG
Fruits Banana sell 50
Fruits Banana surplus 23
Fruits Banana Unit Price 7
Pets kitten sell 3
Pets kitten surplus 5

SQL statement:

SELECT TYPE,NAME
, sum (decode (class, 'sell', num_ Flag, 0)) as sold
, sum (decode (class, 'remaining', num_ Flag, 0)) as remaining
, sum (decode (class, 'unit price', num_ Flag, 0)) as unit price
FROM TEMP_TABLE 
GROUP BY TYPE,NAME

Effect after execution

TYPE NAME sell surplus Unit Price
Fruits Banana 50 23 7
Pets kitten 3 5 0