Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help (Oracle 11g)
SQL Query Help [message #644761] Mon, 16 November 2015 23:59 Go to next message
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 #644764 is a reply to message #644761] Tue, 17 November 2015 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please find better titles to your topics than "SQL Query Help" or "Need Help in SQL Query"; remember your topics has also the purpose to help others that search for the same question.

This is a standard pivot query.
As you are in 11g have a look at SELECT page of SQL reference, especially PIVOT clause (link in your previous topic).
For all versions solution, search for "MAX(DECODE" here or on the web.

Note: If you want to remove the NULLs (but the last ones) then you have to also use ROW_NUMBER function.
Re: SQL Query Help [message #644771 is a reply to message #644764] Tue, 17 November 2015 03:46 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Please help me with the query
Re: SQL Query Help [message #644773 is a reply to message #644771] Tue, 17 November 2015 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please try to find it by yourself and come back with your tries.

Re: SQL Query Help [message #644776 is a reply to message #644773] Tue, 17 November 2015 04:11 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Please can you tell me where i can take Oracle SQL & PL-SQL Training. As i am learning from net & practicing these stuff. I i get a good tutor i will be able to understand all the contents in details.

Not able to figure out how to go for the above solution as provided.

Sorry for the inconvenience, I m not expecting a query but if i get a good reference who can teach me Oracle it would be great help.
Re: SQL Query Help [message #644778 is a reply to message #644776] Tue, 17 November 2015 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you try to search what I said?
There are examples of PIVOT clause in SQL Reference and in this forum.

[Updated on: Tue, 17 November 2015 04:18]

Report message to a moderator

Re: SQL Query Help [message #645127 is a reply to message #644778] Fri, 27 November 2015 01:06 Go to previous messageGo to next message
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

Re: SQL Query Help [message #645140 is a reply to message #645127] Fri, 27 November 2015 06:00 Go to previous message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Thanks a lot Champ: Michel Cadot

Love your work and appreciate your help Smile Thumbs Up
Previous Topic: count of tests
Next Topic: SQL Query Help
Goto Forum:
  


Current Time: Mon Mar 18 21:07:27 CDT 2024