合 Oracle中的行转列
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; |