SQL Query Help [message #644761] |
Mon, 16 November 2015 23:59 |
|
arifs3738
Messages: 39 Registered: November 2015 Location: India
|
Member |
|
|
Question: List employees under their own department name like?
DEPT10 DEPT20 DEPT30
SCOTT NULL NULL
NULL ROBERT NULL
NULL NULL JOHN
SQL QUERY:
SELECT E.*,
(CASE WHEN DEPTNO=10 THEN ENAME
ELSE NULL
END) AS DEPT_10,
(CASE WHEN DEPTNO=20 THEN ENAME
ELSE NULL
END) AS DEPT_20,
(CASE WHEN DEPTNO=30 THEN ENAME
ELSE NULL
END) AS DEPT_30
FROM EMP E
ORDER BY DEPT_10, DEPT_20, DEPT_30;
Is there a good approach to write SQL query which i have written may be via join or anything as i am learning sql.
|
|
|
|
|
|
|
|
Re: SQL Query Help [message #645127 is a reply to message #644778] |
Fri, 27 November 2015 01:06 |
|
Michel Cadot
Messages: 68617 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Solutions:
SQL> select max(decode(deptno,10,ename)) dept10,
2 max(decode(deptno,20,ename)) dept20,
3 max(decode(deptno,30,ename)) dept30
4 from emp
5 group by empno, ename
6 order by ename, empno
7 /
DEPT10 DEPT20 DEPT30
---------- ---------- ----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
SQL> select "10" dept10, "20" dept20, "30" dept30
2 from emp pivot (max(ename) for deptno in (10,20,30))
3 /
DEPT10 DEPT20 DEPT30
---------- ---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
|
|
|
|