Oracle row-to-column and column-changing careers

Source: Internet
Author: User

First, row to column
1.1. Initial test data

Table structure: Test_tb_grade

SQL code:

1 CREATE TABLE Test_tb_grade
2 (
3 ID Number (TEN) is not NULL,
4 user_name VARCHAR2 (CHAR),
5 COURSE VARCHAR2 (CHAR),
6 score FLOAT
7)

Initial data such as:

 
1.2, if you need to implement the following query:


This is the most common row-to-column, the main principle is to use the Decode function, aggregation function (sum), combined with the group by group implementation, the specific SQL is as follows:

SQL code:
1    select t.user_name,
2      sum (decode (t.course, ' language ', score,null)) as Chinese,
3      sum (decode (t.course, ' math ', score,null) as MATH,
4      SUM (Decode (t.course, ' English ', score,null)) as 中文版
5    from test_tb_grade t
6     GROUP BY T.user_name
7    order by T.user_name
1.3, extension

If you want to achieve a different score segment for each subject, as follows:
 

 


The specific implementation of SQL is as follows:
SQL code:

T2 Select. SCORE_GP,
The sum of decode (t2.course, ' language ', countnum,null) as Chinese,
SUM (Decode (t2.course, ' math ', countnum,null) as MATH,
SUM (Decode (t2.course, ' English ', countnum,null)) as 中文版
From (
Select T.course,
When the case was T.score <60 then ' 00-60 '
T.score >=60 and T.score <80 then ' 60-80 '
T.score >=80 Then ' 80-100 ' End as SCORE_GP,
Ten count (T.score) as Countnum
From Test_tb_grade t
Group BY T.course, 
When T.score <60 then ' 00-60 '
T.score >=60 and T.score <80 then ' 60-80 '
T.score >=80 Then ' 80-100 ' end
ORDER by T.course) T2
GROUP by T2. Score_gp
ORDER by T2. Score_gp
Second, the list of career change

1.1. Initial test data
Table structure: Test_tb_grade2 
SQL code:
1 CREATE TABLE Test_tb_grade2
2 (
3 ID Number (TEN) is not NULL,
4 user_name VARCHAR2 (CHAR),
5 Cn_score FLOAT,
6 Math_score FLOAT,
7 En_score FLOAT
8)

Initial data such as:

1.2, if you need to implement the following query:

This is the most common column change, the main principle is to use the union inside the SQL, the specific SQL statements are as follows:
SQL code: 

1 Select user_name, ' Cn_score ' COURSE, Cn_score as score from Test_tb_grade2 
2 Union
3 Select user_name, ' Math_score ' COURSE, Math_score as score from Test_tb_grade2 
4 Union
5 Select user_name, ' En_score ' COURSE, En_score as score from Test_tb_grade2 
6 ORDER BY User_name,course

You can also make use of the insert all into ... Select to first build a table test_tb_grade3:
SQL code:
1 CREATE TABLE Test_tb_grade3
2 ( 
3 user_name VARCHAR2 (CHAR),
4 COURSE VARCHAR2 (CHAR),
5 Score FLOAT
6)

Then execute the following sql: 
SQL code:
1 Insert All
2 into Test_tb_grade3 (User_name,course,score) VALUES (user_name, ' languages ', Cn_score)
3 into Test_tb_grade3 (User_name,course,score) VALUES (user_name, ' math ', Math_score)
4 into Test_tb_grade3 (User_name,course,score) VALUES (user_name, ' English ', En_score)
5 Select user_name, Cn_score, Math_score, en_score from Test_tb_grade2;
6 commit;

Do not forget the commit operation, and then query the TEST_TB_GRADE3, found that the table data is the column to go.

Oracle row-to-column and column-changing careers

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.