oracle 行转列和列转行
1 WITH L AS 2 (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 9 ), 3 m AS ( 4 SELECT A.LV AS LV_A, 5 B.LV AS LV_B, 6 TO_CHAR(B.LV) || 'x' || TO_CHAR(A.LV) || '=' || 7 RPAD(TO_CHAR(A.LV * B.LV), 2, ' ') AS TEXT 8 FROM L A, L B 9 WHERE b.LV <= a.LV ) 10 SELECT listagg( m.text,' ') WITHIN GROUP (ORDER BY m.lv_b ) AS 九九表 11 FROM m 12 GROUP BY m.lv_a 13 ;
列转行 练习题；
1 SELECT 部门,nvl(职员,0.00),nvl(推销员,0.00),nvl(董事长,0.00),nvl(总经理,0.00),nvl(分析员,0.00) 2 FROM ( 3 SELECT 4 to_char(a.deptno) AS 部门, 5 MIN(DECODE(a.job,'CLERK',a.avg_sal)) 职员, 6 MIN(DECODE(a.job,'SALESMAN',a.avg_sal)) 推销员, 7 MIN(dECODE(a.job,'PRESIDENT',a.avg_sal)) 董事长, 8 MIN(DECODE(a.job,'MANAGER',a.avg_sal)) 总经理, 9 MIN(DECODE(a.job,'ANALYST',a.avg_sal)) 分析员 10 FROM ( 11 SELECT 12 e.deptno , 13 e.job, 14 ROUND (AVG(sal),5) avg_sal 15 FROM emp e 16 GROUP BY e.job,e.deptno 17 ORDER BY e.deptno 18 ) a 19 GROUP BY a.deptno 20 ORDER BY a.deptno 21 ) 22 UNION ALL 23 SELECT 部门,nvl(职员,0.00),nvl(推销员,0.00),nvl(董事长,0.00),nvl(总经理,0.00),nvl(分析员,0.00) 24 FROM( 25 SELECT 26 '职位总和' AS 部门, 27 max(sum(DECODE(job,'CLERK',sal))) AS 职员, 28 max(sum(DECODE(job,'SALESMAN',sal))) AS 推销员, 29 max(sum(DECODE(job,'PRESIDENT',sal))) AS 董事长, 30 max(sum(DECODE(job,'MANAGER',sal))) AS 总经理, 31 max( sum(DECODE(job,'ANALYST',sal))) AS 分析员 32 FROM emp 33 GROUP BY job 34 35 ) 36 UNION ALL 37 SELECT 部门,nvl(职员,0.00),nvl(推销员,0.00),nvl(董事长,0.00),nvl(总经理,0.00),nvl(分析员,0.00) 38 FROM( 39 SELECT 40 '平均值' AS 部门, 41 min(DECODE(job,'CLERK',ROUND (AVG(sal),5))) AS 职员, 42 min(DECODE(job,'SALESMAN',ROUND (AVG(sal),5))) AS 推销员, 43 min(DECODE(job,'PRESIDENT',ROUND (AVG(sal),5))) AS 董事长, 44 min(DECODE(job,'MANAGER',ROUND (AVG(sal),5))) AS 总经理, 45 min(DECODE(job,'ANALYST',ROUND (AVG(sal),5))) AS 分析员 46 FROM emp 47 GROUP BY job 48 49 );
上例题的简化版 ：
1 /* 2 1.先用使用decode()进行行转列查出基础数据作为A表！ ; ; 3 2.B表对A表中的数据进行汇总然后进行合并; 4 */ 5 WITH a AS ( 6 SELECT 7 to_char(deptno) AS deptno , 8 SUM(DECODE(job,'ANALYST',sal,0)) AS ANALYST, 9 SUM(DECODE(job,'CLERK',sal,0)) AS CLERK, 10 SUM(DECODE(job,'MANAGER',sal,0)) AS MANAGER, 11 SUM(DECODE(job,'PRESIDENT',sal,0)) AS PRESIDENT, 12 SUM(DECODE(job,'SALESMAN',sal,0)) AS SALESMAN , 13 SUM(sal) AS 总和 14 FROM scott.EMP 15 GROUP BY deptno 16 ORDER BY 1 ), 17 b AS ( 18 SELECT '总和', 19 sum(ANALYST), 20 sum(CLERK), 21 sum(MANAGER), 22 sum(PRESIDENT), 23 sum(SALESMAN) , 24 SUM(总和) FROM a ) 25 SELECT * FROM a 26 UNION ALL 27 SELECT * FROM b ;
