SQL Fundamentals SQL column field processing SQL (case, decode, row to column)

handling of column fields SQL Case Do the column processing Sql> Select deptno,sum (SAL) from the EMP Group by DEPTNO; Select Case When deptno=10 Then ' ACCOUNTING ' When deptno=20 Then ' Reserch ' When deptno=30 Then ' SALES ' End sum (SAL) from EMP GROUP BY Deptno

handling of column fields SQL Decode There is also the case that when nothing is the case, use a value instead Objective The DECODE () function, which compares the input value to the parameter list in the function, and returns a corresponding value based on the input value. The parameter list of a function is a number of sequential pairs consisting of several numeric values and their corresponding result values. Of course, the function also has a default return value if it fails to match any one of the actual argument order pairs successfully. The syntax structure is as follows: Decode (expression, search_1, result_1) Decode (expression, search_1, Result_1, search_2, result_2) Decode (expression, search_1, Result_1, search_2, Result_2, ....., Search_n, Result_n) Decode (expression, search_1, result_1, default) Decode (expression, search_1, Result_1, search_2, result_2, default) Decode (expression, search_1, Result_1, search_2, Result_2, ....., Search_n, result_n, default) Compares expressions and search words, returns the result if it matches, returns a default value if it does not match, or returns a null value if no default value is defined. Select Decode (deptno, Ten, ' ACCOUNTING ', The ' Reserch ', The ' SALES ' ), sum (SAL) from EMP GROUP BY Deptno

Row to Column Sql> Select Job,ename,sal from emp where job= ' MANAGER '; JOB ename SAL --------- ---------- ---------- MANAGER JONES 2975 MANAGER BLAKE 2850 MANAGER CLARK 2450 Sql> sql> Select Job,decode (ename, ' BLAKE ', Sal) Blake,decode (ename, ' JONES ', Sal) Jones,decode (ename, ' CLARK ', Sal) CLARK from emp where job= ' MANAGER '; JOB BLAKE JONES CLARK --------- ---------- ---------- ---------- MANAGER 2975 MANAGER 2850 MANAGER 2450 sql> Select Job, sum (decode (ename, ' BLAKE ', SAL)) BLAKE, Sum (Decode (ename, ' JONES ', SAL)) JONES , sum (decode (ename, ' CLARK ', SAL)) CLARK from emp where job= ' MANAGER ' GROUP BY Job ; JOB BLAKE JONES CLARK --------- ---------- ---------- ---------- MANAGER 2850 2975 2450 sql>Select Job,avg(decode (ename, ' BLAKE ', SAL))BLAKE,Max(Decode (ename, ' JONES ', SAL))JONES,min(decode (ename, ' CLARK ', SAL))CLARK from emp where job= ' MANAGER 'GROUP BY Job; JOB BLAKE JONES CLARK --------- ---------- ---------- ---------- MANAGER 2850 2975 2450

SQL Fundamentals SQL column field processing SQL (case, decode, row to column)