Thinking and summarizing of Oracle's rank and column transformation __oracle

In recent days has been to oracle-sql problems, involving some of the usual not used things, but also here depressed for a long time. Now the problem has been solved, though not perfect. But let's share it with you.

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

This may also be the first way we encounter a row-and-column transformation. The ingenious use of summary and judgment statements can be solved.

First look at a simple basic table: the following figure

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

There are two people in the table, and each line represents each student's achievement in the subject. This is also the beginning of our beginner SQL is a very common table structure.

Requirements: The above multiline value to the student unit into 2 line values, can be very intuitive to see each student's grades. The following statement is then available.

Select
S.stuname,
SUM (case when s.subjectname= ' language ' then S.subjectscore else 0 end) as language grade,
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 implementation:

The above practice is also very easy to understand, here is not much to say, but the above is also limited. Because there are some fixed values here. such as "Chinese, maths," in the case. "But when you meet a small business need, you can use it directly," he said. For example, a student performance management system This can be satisfied.

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

Material or the table above, I want to get the following results:

The attributes of an object are connected to a column and stored in a string.

When I saw this business demand, of course, students this is just for example. There's no clue. But the business is also anxious to data, there is no way. Had to bite the bullet to find a way. First of all, I think of this function to use the connection string, but also need to judge the loop and so on. So it's remembered. To do so, to create 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);--This method returns a string.
BEGIN
For RS in
(
SELECT s.subjectname| | ': ' | | S.subjectscore as Allshow
From student S
Where s.stuname=parameter--when parameters are always eligible for loop insert result set 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 the modified method makes a judgment on each row value and produces many records. Then select DISTINCT S.stuname,getallsubject (s.stuname) from student s

Effect:

Got the desired result.

Method 2: Using the Sys_connect_by_path of Oracle self-band

To illustrate the following:

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

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 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--creates a tree relationship, name can be used as a parentid,rank childid
Connect by sname = Prior sname and rank-1 = Prior rank
)
)
where ename_path_rank=1;

Execution results:

And 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 using the above two methods. But both of these methods have some advantages and disadvantages. But I suggest using the following method is more reliable.

Although this problem is solved by combining the above methods, the problem of the character type value in Oracle cannot be greater than 4000 bytes has not been solved. I was wondering if there was a data type, how many bytes I could put into it and how many bytes I could put into it. Welcome you to master guidance. Hope you can help someone who has the problem or is about to meet the problem.

2012-6-18 16:59:51