| Out of SQL Query [message #644397] |
Wed, 04 November 2015 05:45  |
 |
arifs3738
Messages: 39
Registered: November 2015
Location: India
|
Member |
|
|
Hi All,
I have a Emp table as below:
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
OUTPUT REQUIRED:
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPT10 DEPT20 DEPT30
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------- ------ ------
7369 SMITH CLERK 7902 17-DEC-80 800 20 NULL SMITH NULL
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 NULL NULL ALLEN
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 NULL NULL WARD
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
In Ouptput i want 3 columns viz. DEPT10, DEPT20 & DEPT30. If Employee belongs to Dept10 then his name should be written under dept10 column and NULL in dept20 & dept30...
Please provide a approach to write the query.
[Edit MC: add code tags, remove useless repeated heading lines]
[Updated on: Fri, 27 November 2015 00:36] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Out of SQL Query [message #644401 is a reply to message #644399] |
Wed, 04 November 2015 06:33  |
|
arifs3738
Messages: 39
Registered: November 2015
Location: India
|
Member |
|
|
Yes Sir....
Below is the o/p which i wanted to achieve
List employees under their own department name like?
DEPT10 DEPT20 DEPT30
SCOTT NULL NULL
NULL ROBERT NULL
NULL NULL JOHN
QUERY#1:
SELECT E.*, ENAME as DEPT_10, NULL as DEPT_20, NULL AS DEPT_30
FROM EMP E
WHERE DEPTNO=10
UNION ALL
SELECT E.*, NULL as DEPT_10, ENAME as DEPT_20, NULL AS DEPT_30
FROM EMP E
WHERE DEPTNO=20
UNION ALL
SELECT E.*, NULL as DEPT_10, NULL as DEPT_20, ENAME AS DEPT_30
FROM EMP E
WHERE DEPTNO=30;
QUERY#2:
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;
As i am new to Oracle, this is what i thought. Any improvements can be made in this query, ready to learn that.
Thanks all
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
