Oracle row-to-column and column-changing careers

1  withL as2(SELECT  Level  asLv fromDUAL CONNECT by  Level <= 9  ),3M as (4 SELECTa.lv asLv_a,5b.lv asLv_b,6To_char (b.lv)|| 'x' ||To_char (a.lv)|| '=' ||7Rpad (To_char (a.lv*B.LV),2,' ') as TEXT8    froml A, l B9  WHEREb.lv<=a.lv)Ten  SELECTListagg (M.text,' ') withinGROUP(ORDER  byM.lv_b) as99 Table One   fromm A  GROUP  bym.lv_a -;

Set up practice exercises;

1 SELECTDepartment, NVL (staff,0.00), NVL (salesman,0.00), NVL (Chairman,0.00), NVL (general manager,0.00), NVL (analyst,0.00)2  from (3 SELECT4To_char (A.deptno) asDepartment,5       MIN(DECODE (A.job,'Clerk', A.avg_sal)) Staff,6       MIN(DECODE (A.job,'salesman', A.avg_sal)) Salesman,7       MIN(DECODE (A.job,'President', A.avg_sal)) Chairman,8       MIN(DECODE (A.job,'MANAGER', A.avg_sal)) General Manager,9       MIN(DECODE (A.job,'ANALYST', A.avg_sal)) AnalystTen  from ( One SELECT A E.deptno, - E.job, -       ROUND(AVG(SAL),5) Avg_sal the  fromEMP E - GROUP  byE.job,e.deptno - ORDER  byE.deptno - ) A + GROUP  byA.deptno - ORDER  byA.deptno + ) A UNION  All  at SELECTDepartment, NVL (staff,0.00), NVL (salesman,0.00), NVL (Chairman,0.00), NVL (general manager,0.00), NVL (analyst,0.00) -   from(  - SELECT  -         'position sum'  asDepartment, -         Max(sum(DECODE (Job,'Clerk', Sal))) asStaff, -         Max(sum(DECODE (Job,'salesman', Sal))) assalesman, in         Max(sum(DECODE (Job,'President', Sal))) aschairman, -         Max(sum(DECODE (Job,'MANAGER', Sal))) asGeneral Manager, to        Max(sum(DECODE (Job,'ANALYST', Sal))) asAnalyst +  fromEMP - GROUP  byJob the  * )  $ UNION  AllPanax Notoginseng SELECTDepartment, NVL (staff,0.00), NVL (salesman,0.00), NVL (Chairman,0.00), NVL (general manager,0.00), NVL (analyst,0.00) -   from(  the SELECT  +         'Average'  asDepartment, A         min(DECODE (Job,'Clerk',ROUND(AVG(SAL),5))) asStaff, the         min(DECODE (Job,'salesman',ROUND(AVG(SAL),5))) assalesman, +         min(DECODE (Job,'President',ROUND(AVG(SAL),5))) aschairman, -         min(DECODE (Job,'MANAGER',ROUND(AVG(SAL),5))) asGeneral Manager, $         min(DECODE (Job,'ANALYST',ROUND(AVG(SAL),5))) asAnalyst $  fromEMP - GROUP  byJob -  the);

A simplified version of the example above:

1 /*2 1. First use decode () to perform row-to-column detection of basic data as a table!;;3 The 2.B table summarizes the data in table A and merges it;4 */ 5  withA as ( 6 SELECT 7To_char (DEPTNO) asDeptno,8       SUM(DECODE (Job,'ANALYST', Sal,0)) asANALYST,9       SUM(DECODE (Job,'Clerk', Sal,0)) asClerk,Ten       SUM(DECODE (Job,'MANAGER', Sal,0)) asMANAGER, One       SUM(DECODE (Job,'President', Sal,0)) asPresident, A       SUM(DECODE (Job,'salesman', Sal,0)) assalesman, -       SUM(SAL) assum -  fromScott. EMP the GROUP  byDeptno - ORDER  by 1 ), -B as ( - SELECT 'sum',  +        sum(ANALYST), -        sum(Clerk), +        sum(MANAGER), A        sum(President), at        sum(salesman), -        SUMSum froma) - SELECT *  froma - UNION  All  - SELECT *  fromb;

Oracle row-to-column and column-changing careers