Oracle stored procedure is a great god genuflect is begged for help-CodePudding

Table A
The field name mobileNo identityNo createTime updateTime
Table B
The field name, type, No createTime updateTime
Question: the watch to get the data of table B, Type for two types (mobileNo identityNo) No for (mobileNo identityNo) the value of the corresponding
Is the data in A table according to mobileNo identityNo article is divided into two insert table B

CodePudding user response:

The building Lord baidu unpivot, just for you now

CodePudding user response:

Simple, should be suitable for me, I'll try, thank you

CodePudding user response:

reference 1st floor wmxcn2000 response:

the building Lord baidu unpivot, right now you scene

Like this can only be used for the current table right? I want to change after inserting a new table

CodePudding user response:

The

reference 3 floor qq_33759773 response:

like this can only be used for the current watch right? I want to change to insert a new table after

Insert into t (... )
The select...
The from XXXX
Unpivot...

CodePudding user response:

Your question actually is the issue of transfer line column

Turn in one line of thinking: the column of

Now that the Type of two types (mobileNo identityNo)

So you can use the case to judgment or decode implementing

 
- create a test table 

The create table student_score (

Name varchar2 (20), 

The subject varchar2 (20), 

Score number (4, 1) 

); 



-- -- -- -- -- 
 insert test data
Insert into student_score (name, subject, score) values (' zhang ', 'language', 78); 
Insert into student_score (name, subject, score) values (' zhang ', 'mathematics', 88); 
Insert into student_score (name, subject, score) values (' zhang ', 'English', 98); 
Insert into student_score (name, subject, score) values (' bill ', 'language', 89); 
Insert into student_score (name, subject, score) values (' bill ', 'mathematics', 76); 
Insert into student_score (name, subject, score) values (' bill ', 'English', 90); 
Insert into student_score (name, subject, score) values (' Cathy ', 'language', 99); 
Insert into student_score (name, subject, score) values (' Cathy ', 'mathematics', 66); 
Insert into student_score (name, subject, score) values (' Cathy ', 'English', 91); 



-- -- -- -- -- decode rows to columns 

Select the name "name", 

The sum (decode (subject, the 'language', NVL (score 0), 0)) "language," 

The sum (decode (subject, 'mathematics' NVL (score 0), 0)) "math", 

The sum (decode (subject, 'English' NVL (score 0), 0)) 
 "English"
The from student_score 

Group by name; 



-- -- -- -- -- - case when rows to columns 



Select the name "name", 

The sum (the case when the subject='language' 

Then NVL (score 0) 
The else 0 

End) "language", 

The sum (the case when the subject='mathematics' 

Then NVL (score 0) 

The else 0 

End) "math", 

The sum (the case when the subject='English' 

Then NVL (score 0) 

The else 0 

End) "English" 

The from student_score 

Group by name;

After the conversion to join the new table is based on the add a

Insert into table statement