Oracle中的行转列
👉 本文共约121个字，系统预计阅读时间或需1分钟。
max+decode或case函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | SELECT deptno, MAX(DECODE(ename, 'SMITH', sal)) AS SMITH, MAX(DECODE(ename, 'ALLEN', sal)) AS ALLEN, MAX(DECODE(ename, 'WARD', sal)) AS WARD, MAX(DECODE(ename, 'JONES', sal)) AS JONES, MAX(DECODE(ename, 'MARTIN', sal)) AS MARTIN, MAX(DECODE(ename, 'BLAKE', sal)) AS BLAKE, MAX(DECODE(ename, 'CLARK', sal)) AS CLARK, MAX(DECODE(ename, 'SCOTT', sal)) AS SCOTT, MAX(DECODE(ename, 'KING', sal)) AS KING, MAX(DECODE(ename, 'TURNER', sal)) AS TURNER, MAX(DECODE(ename, 'ADAMS', sal)) AS ADAMS, MAX(DECODE(ename, 'JAMES', sal)) AS JAMES, MAX(DECODE(ename, 'FORD', sal)) AS FORD, MAX(DECODE(ename, 'MILLER', sal)) AS MILLER FROM scott.emp GROUP BY deptno; SELECT deptno, MAX(CASE ename WHEN 'SMITH' THEN sal ELSE NULL END) AS SMITH, MAX(CASE ename WHEN 'ALLEN' THEN sal ELSE NULL END) AS ALLEN, MAX(CASE ename WHEN 'WARD' THEN sal ELSE NULL END) AS WARD, MAX(CASE ename WHEN 'JONES' THEN sal ELSE NULL END) AS JONES, MAX(CASE ename WHEN 'MARTIN' THEN sal ELSE NULL END) AS MARTIN, MAX(CASE ename WHEN 'BLAKE' THEN sal ELSE NULL END) AS BLAKE, MAX(CASE ename WHEN 'CLARK' THEN sal ELSE NULL END) AS CLARK, MAX(CASE ename WHEN 'SCOTT' THEN sal ELSE NULL END) AS SCOTT, MAX(CASE ename WHEN 'KING' THEN sal ELSE NULL END) AS KING, MAX(CASE ename WHEN 'TURNER' THEN sal ELSE NULL END) AS TURNER, MAX(CASE ename WHEN 'ADAMS' THEN sal ELSE NULL END) AS ADAMS, MAX(CASE ename WHEN 'JAMES' THEN sal ELSE NULL END) AS JAMES, MAX(CASE ename WHEN 'FORD' THEN sal ELSE NULL END) AS FORD, MAX(CASE ename WHEN 'MILLER' THEN sal ELSE NULL END) AS MILLER FROM scott.emp GROUP BY deptno; |
pivot函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | with temp as( select '四川省' nation ,'成都市' city,'第一' ranking from dual union all select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual ) select * from (select nation,city,ranking from temp) pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四)); SELECT * FROM ( SELECT deptno, ename, sal FROM scott.emp ) PIVOT ( MAX(sal) FOR ename IN ('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER') ); |
