Home » SQL & PL/SQL » SQL & PL/SQL » Pivot output in equijoin (Oracle 10g release 10.2.0.1.0)
Pivot output in equijoin [message #576968] Mon, 11 February 2013 07:45 Go to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi,
I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.

Standard result of joining two tables looks like below:

deptno dname empname
10     ACCT    SCOTT   
10     ACCT    MILLER   
20     SALES   JOHN    
20     SALES   XYZ   
30     FINANCE AAA 


Now I need the output as below as per a report requirement.
entity_type name/no
DEPT        10
EMP         SCOTT
EMP         MILLER
DEPT        20
EMP         JOHN
EMP         XYZ 



I am using oracle 10g release 10.2.0.1.0.

Can I use oracle analytic function here? Can anyone please help?

Regards,

[Updated on: Mon, 11 February 2013 08:55] by Moderator

Report message to a moderator

Re: Pivot output in equijoin [message #576978 is a reply to message #576968] Mon, 11 February 2013 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CODE tags and not quote ones to format.

SQL> select decode(grouping(ename), 0, 'EMP', 'DEPT') entity_type,
  2         decode(grouping(ename), 0, ename, to_char(deptno)) "NAME/NO"
  3  from emp
  4  group by rollup(deptno, ename)
  5  having grouping(deptno) = 0
  6  order by deptno, decode(grouping(ename), 1, ' ', ename)
  7  /
ENTI NAME/NO
---- ----------------------------------------
DEPT 10
EMP  CLARK
EMP  KING
EMP  MILLER
DEPT 20
EMP  ADAMS
EMP  FORD
EMP  JONES
EMP  SCOTT
EMP  SMITH
DEPT 30
EMP  ALLEN
EMP  BLAKE
EMP  JAMES
EMP  MARTIN
EMP  TURNER
EMP  WARD

Regards
Michel
Re: Pivot output in equijoin [message #576984 is a reply to message #576968] Mon, 11 February 2013 09:59 Go to previous message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
And without HAVING clause:

select  decode(grouping(ename),0,'EMP','DEPT') entity_type,
        decode(grouping(ename),0,ename,to_char(deptno)) "NAME/NO"
  from  emp
  group by grouping sets((deptno),(deptno,ename))
  order by deptno,
           grouping(ename) desc,
           ename
/

ENTI NAME/NO
---- ------------
DEPT 10
EMP  CLARK
EMP  KING
EMP  MILLER
DEPT 20
EMP  ADAMS
EMP  FORD
EMP  JONES
EMP  SCOTT
EMP  SMITH
DEPT 30

ENTI NAME/NO
---- ------------
EMP  ALLEN
EMP  BLAKE
EMP  JAMES
EMP  MARTIN
EMP  TURNER
EMP  WARD

17 rows selected.

SQL> 


SY.
Previous Topic: Sending mail when DBMS_SCHEDULER job fails
Next Topic: Table Creation Problem
Goto Forum:
  


Current Time: Tue Sep 16 15:01:47 CDT 2014

Total time taken to generate the page: 0.08889 seconds