Oracle row and column Devices

The project collects statistics on the "reagent" and "consumables" purchase amount and number of purchases by each research group. At that time, it queries and collects statistics on the "reagent" purchase amount and number of purchases, then, query and count the purchase amount and number of purchases of "consumables. This method is inefficient, and it repeatedly accesses the database. If Oracle provides row and column device functions, this problem can be well solved.

Oracle 11g provides piovt

Oracle 10 Gb provides decode

The logic of decode is as follows:

Decode (value, if1, then1, if2, then2, if3, then3,... else)

If value equals to if1, the decode result is then1; If value equals to if2, the result is then2; If value equals to if3, the result is then3. If value does not match any value, returns false. Class is the switch with C:

 Swtch (value ){  Case   "  If1  " : "  Then1  " ; Break ;  Case   "  If2  " : "  Then2  " ; Break  ;  Default : "  Other  " ; Break  ;} 

 

Since I use ORACLE 10 Gb, decode is used as an example:

 
1 Select Sum(Sal), DT. dname2FromEMP t3JoinDept dt4OnT. deptno=DT. deptno5Group ByDname
 

Display result:

8750 Accounting
10875 Research
9400 sales

After device:

Select   Sum (Decode (Dt. dname, '  Accounting  '  , Sal) accounting,  Sum (Decode (Dt. dname, '  Research  '  , Sal) Research,  Sum (Decode (Dt. dname, '  Sales  ' , Sal) Sales  From  EMP t  Join  Dept dt  On T. deptno = DT. deptno

Display result:

Accounting Research sales

8750 10875 9400

The modification statistics on reagent consumables are as follows:

 Select   Sum (Decode (producttype, '  Reagent  ' , OD. sumprice) sjprice,  Count (Decode (producttype, '  Reagent  '  , OD. sumprice) sjcount,  Sum (Decode (producttype, '  Consumables  '  , OD. sumprice) hcprice,  Count (Decode (producttype, '  Consumables  ' , OD. sumprice) hccount, Nd. Department  From  Order_info o  Join  Order_details OD  On O. orderid =  OD. orderid  Join  User_info u  On U. ID =  O. userid  Join  Neodepart nd On Nd. ID =  U. Department  Where   1   =   1   Group   By Nd. Department