In the process of database development, it is often necessary to convert rows into columns or convert columns into rows to display data. However, when creating a table structure, we often cannot store data according to the display requirements, therefore, you can only do some necessary operations (for example, creating a view) after saving the data to achieve the display purpose. The following uses a common data display
In the process of database development, it is often necessary to convert rows into columns or convert columns into rows to display data. However, when creating a table structure, we often cannot store data according to the display requirements, therefore, you can only do some necessary operations (for example, creating a view) after saving the data to achieve the display purpose. The following uses a common data display
During development, many times we need to convert rows into columns or convert columns into rows to display data. However, when creating a table structure, we often cannot store data according to the display requirements, therefore, you can only do some necessary operations (for example, creating a view) after saving the data to achieve the display purpose.
A common data display is used to decode the function. Is the display of the transcript, which is the most common in the teaching management system. I want to develop people who have met this problem and often come into contact with the transcript during college, showing the name, Chinese, and mathematics.
The implementation script is as follows (cjd. SQL ):
-- Create a table
Create table stud
(
Sid varchar2 (10 ),
Kcbm varchar2 (10 ),
Cj int
);
-- Insert Test Data
Insert into stud values ('1', 'China', 80 );
Insert into stud values ('2', 'mat', 90 );
Insert into stud values ('3', 'inc', 100 );
Commit;
-- Create a view and decode usage
Create or replace view cjd
Select sid,
Decode (kcbm, 'China', cj, 0) language,
Decode (kcbm, 'mate', 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', 'China', 80 );
One row has been created.
SQL> insert into stud values ('2', 'mat', 90 );
One row has been created.
SQL> insert into stud values ('3', 'English ', 100 );
One row has been created.
SQL> commit;
Submitted.
SQL> create or replace view cjd
2 select sid,
3 decode (kcbm, 'China', cj, 0) language,
4 decode (kcbm, 'mat', cj, 0) mathematics,
5 decode (kcbm, 'English ', cj, 0) English
6 from stud
7 order by sid;
The view has been created.
SQL> select * from cjd;
SID Chinese maths English
----------------------------------------
1 80 0 0
2 0 90 0
3 0 100
