Oracle Data Display horizontal table to vertical table

1. Create a table
Copy codeThe Code is as follows:
-- Create table
Create table test
(
Dm1 char (3 ),
Dm2 char (3 ),
Mc1 nvarchar2 (20 ),
Val nvarchar2 (20)
)

2. Fill in the following data:

DM1	DM2	MC1	VAL
101	1	C1	100
101	1	C2	80
101	1	C3	40
101	2	C1	30
101	2	C2	80
102	4	C1	9
102	6	C2	50

The converted data is shown as follows:

DM1	DM2	C1	C2	C3
101	1	100	80	40
101	2	30	80
102	4	9
102	6		50

3. Conversion statement:
Copy codeThe Code is as follows:
Select dm1, dm2, sum (decode (mc1, 'c1', val) c1, sum (decode (mc1, 'c1', val) c2, sum (decode (mc1, 'c3', val) c3
From test
Group by dm1, dm2

Note: This statement is executed when val is a numeric value. If val is not numeric or contains a string or other types, an error is returned when sum is used, here we can use another method to change sum to max. The statement is as follows:
Copy codeThe Code is as follows:
Select dm1, dm2, max (decode (mc1, 'c1', val) c1, max (decode (mc1, 'c1', val) c2, max (decode (mc1, 'c3', val) c3
From test
Group by dm1, dm2

In addition, the horizontal table to the vertical table can also be implemented using a case statement similar to decode. The principle is the same as that of this statement, which is not described too much here.