Implementation of DB2 column-to-row

DB2 column-to-row is a common operation. The following describes the steps for column-to-row conversion in DB2. If you are interested in column-to-row conversion in DB2, take a look.

The following data is provided:

Create table salespartition
(Year INTEGER,
Q1 INTEGER,
Q2 INTEGER,
Q3 INTEGER,
Q4 INTEGER );
YEAR Q1 Q2 Q3 Q4
-------------------------------------------------------
2004 20 30 15 10
2005 18 40 12 27
Expected results
YEAR QUARTER RESULTS
---------------------------------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12 2005 4 27
This SQL statement can be implemented:

Select s. Year, Q. Quarter, Q. Results
FROM salesclerk as s,
TABLE (VALUES (1, S. q1 ),
(2, S. q2 ),
(3, S. q3 ),
(4, S. q4 ))
As q (Quarter, Results );
The Data Types of the corresponding columns in each values must be the same, and the values can be arbitrary. For example, 1, 2, 3, and 4 are integer. The following describes the execution process: the core is to create a table using the table function, this table is a multi-row table implemented by value. An example of virtual table implemented by value is as follows:
Db2 => select * from (values (1, 2), (2, 3) as t1 (col1, col2)
COL1 COL2
----------------------
1 2
2 3
Two records are selected.
Db2 => select * from (values 1) as

1
-----------
1

1 record selected.

The difference is that a table in the from clause is related here, and a column in the table is taken as data.

Implementation of Online DB2 backup

How to Create a DB2 instance in Windows

DB2 Time Functions

DB2 common table expression usage

Four ways to delete DB2 data