Thinking and Summary of Oracle Row and Column Conversion

Thinking and Summary of Oracle Row and Column Conversion
the
I have been dealing with Oracle-SQL issues in the past few days, and it involves some things that are not usually used, so I have been depressed here for a long time. Now the problem is solved although not perfect. But still share it with everyone.
One of row and column conversion: sum (case when.. then.. else.. end) as statement
This may also be the first row-to-column conversion method we have encountered. It can be solved by clever use of summary and judgment statements.
First look at a simple basic table: as shown below
create table STUDENT
(www.2cto.com
STUNAME NVARCHAR2(50),
SUBJECTNAME NVARCHAR2(50),
SUBJECTSCORE NUMBER
)
the

the
There are two people’s grades in the table, and each row represents the grades of each student in this subject. This is also a very common table structure when we first learn SQL.
Requirement: Convert the above multi-line values ​​into 2-line values ​​in units of students, so that you can intuitively see the grades of each subject of each student. So there is the following statement.
select
s.stuname,
sum(case when s.subjectname='Chinese' then s.subjectscore else 0 end ) as Chinese score,
sum(case when s.subjectname='Mathematics' then s.subjectscore else 0 end ) as math score,
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
The result after execution:


the
The above approach is also very easy for people to understand, so I won’t say much here, but the above also has limitations. Because there are some fixed values ​​here after all. For example, “Chinese, Mathematics…” in the case, but it can still be used directly for small business needs. For example, a student performance management system will suffice.
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 www.2cto.com
The material is still the table above, the result I want to get is:


the
Connect the attributes of various disciplines of an object into a column and store them with strings.
When I saw this business requirement at the time, of course the student was just an example. Very clueless. But the business side is also anxious for data, there is no way. Had to bite the bullet and find a way. First of all, I remembered this function that needs to use the connection string, and it also needs to judge the loop and the like. So I thought of doing this and creating a custom function. To handle the continuous addition of strings.
Method 1: custom function, loop
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 the parameters always meet the conditions, the for loop inserts the 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


the
It can be seen that the modified method judges the value of each row and generates multiple records. Then select distinct s.stuname,getallsubject(s.stuname) from student s
Effect:


the
Got the desired result.
Method 2: Use sys_connect_by_path that comes with oracle
To clarify: www.2cto.com


the
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 follows:


the
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 www.2cto.com
  from stu1 s1 order by s1.sname,s1.sshow–Create a tree relationship, name can be used as parentid, rank can be used as childid
the) connect by sname = prior sname and rank-1 = prior rank
  )
)
where ename_path_rank=1;
Results of the:


the
We also got the desired result. Of course, we can use the replace function to customize without the spacer between subjects.
The more complicated row-column conversion can be realized by the above two methods. But both methods have some advantages and disadvantages. But I suggest to use the following method is more reliable.
the

the
the
the
Author Little People.