1. Table Building
Copy Code code as follows:
--Create table
CREATE TABLE Test
(
DM1 char (3),
DM2 char (3),
MC1 Nvarchar2 (20),
Val Nvarchar2 (20)
)
2. Fill in the data as follows:
| 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 appears as follows:
| DM1 |
DM2 |
C1 |
C2 |
C3 |
| 101 |
1 |
100 |
80 |
40 |
| 101 |
2 |
30 |
80 |
|
| 102 |
4 |
9 |
|
|
| 102 |
6 |
|
50 |
|
3. Conversion statements:
Copy Code code as follows:
Select Dm1,dm2,sum (Decode (MC1, ' C1 ', Val)) C1,sum (Decode (MC1, ' C2 ', Val)) C2,sum (Decode (MC1, ' C3 ', Val)) C3
From Test
GROUP BY DM1,DM2
Note: This statement is the method of execution where Val is numeric, if Val is not numeric, or if it contains a string, and the sum is an error, here's another way to change sum to Max, as follows:
Copy Code code as follows:
Select Dm1,dm2,max (Decode (MC1, ' C1 ', Val)) C1,max (Decode (MC1, ' C2 ', Val)) C2,max (Decode (MC1, ' C3 ', Val)) C3
From Test
GROUP BY DM1,DM2
In addition, the vertical table can also be used with decode meaning similar to the case statement, the principle of the statement, there is no longer too much description.
