The decode function is used to solve the problem of Row-to-column conversion.

If-then-else logic in decode
In logical programming, if-then-else is often used for logical judgment. In decode syntax, this is actually the logic processing process. Its syntax is as follows: Decode (value, if1, then1, if2, then2, if3, then3 ,... else) value indicates any column of any type in a table or any result obtained by calculation. When each value is tested, if the value is if1, the result of the decode function is then1; if the value is if2, the result of the decode function is then2; and so on. In fact, multiple if/then
Pairing. If the value result is not equal to any given pairing, the decode result returns else. Note that if, then, and else can both be functions or computing expressions.

 

The decode function is equivalent to a Condition Statement (IF). It compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value. Unlike other SQL functions, the decode function can also identify and operate null values.
The syntax format is as follows:
Decode (input_value, value, result [, value, result…] [, Default_result]);
Where:
The value that input_value tries to process. The decode function compares the value with a series of sequence dolls to determine the final returned results.
Value is the value of an ordinal pair. If the input value matches the value, the corresponding result is returned. Corresponds to an empty return value. You can use the keyword null
Result is the result value of an ordinal pair.
The default value returned by the function when default_result fails to match any sequence even.
The following example shows how to read the items in the blood_test_flag column of the user checkup table seapark as the supported values of the decode function.
Select checkup_type,
Decode (blood_test_flag, 'y', 'yes', 'n', 'No', null, 'none', 'invalid ')
From checkup;

 

Convert the original columns

Select dwbh, inoutclass, prodclass,
Sum (decode (to_char (sssj, 'yyymmm '), '000000', amount, 0) sj1, -- if the time is 201001, the sum quantity is set. The default value is 0, which is from January 1, 201001.
Sum (decode (to_char (sssj, 'yyymmm '), '000000', amount, 0) sj2,
Sum (decode (to_char (sssj, 'yyymmm '), '000000', amount, 0) sj3
From fact_store where dwbh = '000000' and inouttype = '000000'
Group by dwbh, inoutclass, prodclass -- decode which column is not group by which column

 

Get:

Dwbh inoutclass prodclass sj1 sj2 sj3 -- unit number statistical indicator product time 1 time 2 time 3

300010 100080 210020 0 1639.63 4888.794

....

 

Put the sum of the previous n months into the next column.