首页 > 代码库 > oracle 行转列和列转行
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 ;
oracle 行转列和列转行
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。