In the process of developing database development, there are many times you need to convert the row into columns or convert the columns to rows to display data, and often we can save data according to the requirements of the display when establishing a table structure, so after saving data Do some necessary operations (for example: establish a view, etc.) to achieve the purpose of display.
Next, use a common data display to illustrate the usage of the DECODE function. It is the display of the transcript, which is the most common in the teaching management system. The people I want to do have encountered this, and it is often exposed to transcripts during college, showing: name, language, mathematics, etc.
Implement the script as follows (CJD.sql):
- Construction Table Create Table Stud (Sid Varchar2 (10), KCBM VARCHAR2 (10), CJ INT); - Insert Test Data Insert Into Stud Values ('1', 'Language', 80); Insert Into Stud Values '2', 'Mathematics', 90); Insert Into Stud Values (' 3 ',' English ', 100); Commit; - Create View, Decode Usage Create Or Replace View CJD AS SID, DECODE (KCBM,' Language ', CJ, 0) Language, Decode (KCBM,' Mathematics', CJ, 0) Mathematics, Decode (KCBM, 'English', CJ, 0) English from Stud Order by SID; - Display Data Select * from CJD ;
The execution process is as follows:
SQL> Create Table Stud (Sid Varchar2 (10), 2 KCBM VARCHAR2 (10), 3 CJ INT)
The table has been created.
SQL> INSERT INTO Stud Values ('1', 'Language ", 80);
It has created a row.
SQL> INSERT INTO Stud Values ('2', 'Mathematics ", 90);
It has created a row.
SQL> INSERT INTO Stud Values ('3', 'English', 100);
It has created a row.
SQL> commit;
Submitted.
SQL> Create or Replace View CJD AS 2 SELECT SID, 3 DECODE (KCBM, 'Language ", CJ, 0) Language, 4 Decode (KCBM,' Mathematics ', CJ, 0) Mathematics, 5 Decode (KCBM,' English" , CJ, 0) English 6 from stud 7 ORDER BY SID;
The view has been established.
SQL> SELECT * from CJD;
SID language mathematics English ---------- ------------------ ---------- 1 80 0 0 2 0 90 0 3 0 0 100
