Thinking and summing up of the transformation of Oracle ranks

In recent days has been the problem of oracle-sql, involving a number of things that are not used in peacetime, and therefore here depressed for a long time. Now the problem has been solved, though not perfect. But let's share it with everyone.

One of the row and column conversions: sum (case). Then.. else.. END) as statement

This may also be the first way we have encountered the transformation of the ranks. Clever use of summary and judgment statements can be solved.

Let's look at a simple basic table:

CREATE TABLE STUDENT
(
Stuname NVARCHAR2 (50),
Subjectname NVARCHAR2 (50),
Subjectscore number
)

There are two people in the table, each representing each student's achievement in the subject. This is also a very common form of SQL when we have a table structure.

Requirements: The above multi-line values in the student unit into 2 rows of values, can be very intuitive to see each student's scores. Then there is the following statement.

Select
S.stuname,
SUM (case when S.subjectname= 's language ' then S.subjectscore else 0 end) as language score,
SUM (case when s.subjectname= ' math ' then S.subjectscore else 0 end) as mathematical results,
SUM (case when s.subjectname= ' English ' then S.subjectscore else 0 end) as English score
From student S
GROUP BY S.stuname
ORDER BY S.stuname

Results obtained after execution:

The above approach is also easy to understand, there is not much to say, but the above is also limited. Because there are some fixed values here. For example, in case of "Chinese, mathematics." "But there are small business needs that can be used directly." such as a student performance management system This can be satisfied.

Row and column conversion two: Sys_connect_by_path system function, custom function, connect by sname = Prior sname and rank-1 = prior rank recursive statement

The material or the table above, the result I want to get is:

Connect the attributes of each discipline of an object into a column and store it in a string.

When I saw this business need, of course, students this is just to give an example. There's no clue. But the business is also eager to data, there is no way. Had to bite the bullet and try. The first thing I think about is the function of the connection string, and the need to judge the loop. So I thought about doing this and creating a custom function. To handle the constant addition of strings.

Method 1: Custom functions, looping

CREATE OR REPLACE FUNCTION getallsubject (parameter varchar2)--parameter method, string
RETURN VARCHAR2
Is
RETURN_STR varchar2 (4000);--the method returns a string.
BEGIN
For RS in
(
SELECT s.subjectname| | ': ' | | S.subjectscore as Allshow
From student S
Where s.stuname=parameter--inserts the result set when the parameter is consistently eligible for loop RS
) LOOP
return_str:=return_str| | Rs.allshow;--loop all strings, add
END LOOP;
RETURN Return_str;
END;

Execute select S.stuname,getallsubject (s.stuname) from student s

It can be seen that each row of values is judged by the method, resulting in multiple records. Then select DISTINCT S.stuname,getallsubject (s.stuname) from student s

Effect:

Get the results you want.

Method 2: Leverage Oracle's own Sys_connect_by_path

To illustrate this:

So before using this function, we must first process the source data. The first step is simply to deal with it.

CREATE TABLE STU1 as

Select S.stuname sname,s.subjectname| | S.subjectscore Sshow from student s

--
Select *from stu1

As shown below:

OK, you can write the statement below.

Select Sname as name, Allstr detailed description from
(
Select Sname,allstr,
Row_number () over (partition by sname ORDER BY sname,curr_level Desc) Ename_path_rank
From (
Select Sname,sshow,rank,level as Curr_level,
LTrim (Sys_connect_by_path (sshow, ', '), ', ') allstr from--add all the strings
(
Select S1.sname,s1.sshow,row_number () over (partition by S1.sname ORDER by S1.sname,s1.sshow) rank
From STU1 s1 The order by s1.sname,s1.sshow--to create a tree relationship, name can be used as parentid,rank as a childid
Connect by sname = Prior sname and rank-1 = Prior rank
)
)
where ename_path_rank=1;

Execution Result:

And we get the results we want. Of course, we can use the Replace function to customize the gap between disciplines.

A more complex row and column conversion can be achieved with the above two methods. However, there are some advantages and disadvantages in these two methods. But I suggest using the following method is more reliable.

Although the problem is solved by combining the above methods, the problem of the character type value in Oracle cannot be greater than 4000 bytes is still unresolved. I was wondering if there was a data type, how many bytes I could put into it. Welcome to Master Guide! Hopefully, you'll be able to help people who are experiencing the problem or are going to meet the problem.

Thinking and summing up of the transformation of Oracle ranks