Home » SQL & PL/SQL » SQL & PL/SQL » Complex query
Complex query [message #203486] Wed, 15 November 2006 04:52 Go to next message
AsimAli_5
Messages: 4
Registered: December 2005
Location: PAKISTAN
Junior Member
The data in table is like
Employee_salary(Table):
Emp_sal Month SALARY EMP_ID
1 1-OCT-06 50 20
2 1-SEP-06 55 20
3 1-AUG-06 50 21

I WANT RESULT LIKE

EMP AUG SEP OCT
20 0 55 50
21 50 0 0


KINDLY HELP ME IN CREATING THIS QUERY

THANKS
Junior Member
Asim
Re: Complex query [message #203505 is a reply to message #203486] Wed, 15 November 2006 05:54 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
select b.emp_id, sum(JAN) JAN, sum(FEB) FEB, sum(MAR) MAR, sum(APR) APR, sum(MAY) MAY, sum(JUN) JUN,
        sum(JUL) JUL, sum(AUG) AUG, sum(SEP) SEP, sum(OCT) OCT, sum(NOV) NOV, sum(DEC) DEC
 from (select emp_id,
               decode(trunc(EMP_month,'MONTH'),to_date('01-JAN-2006','dd-MON-YYYY'),salary, 0) JAN,
               decode(trunc(EMP_month,'MONTH'),to_date('01-FEB-2006','dd-MON-YYYY'),salary, 0) FEB,
               decode(trunc(EMP_month,'MONTH'),to_date('01-MAR-2006','dd-MON-YYYY'),salary, 0) MAR, 
               decode(trunc(EMP_month,'MONTH'),to_date('01-APR-2006','dd-MON-YYYY'),salary, 0) APR,
               decode(trunc(EMP_month,'MONTH'),to_date('01-MAY-2006','dd-MON-YYYY'),salary, 0) MAY,
               decode(trunc(EMP_month,'MONTH'),to_date('01-JUN-2006','dd-MON-YYYY'),salary, 0) JUN,                          
               decode(trunc(EMP_month,'MONTH'),to_date('01-JUL-2006','dd-MON-YYYY'),salary, 0) JUL,
               decode(trunc(EMP_month,'MONTH'),to_date('01-AUG-2006','dd-MON-YYYY'),salary, 0) AUG,
               decode(trunc(EMP_month,'MONTH'),to_date('01-SEP-2006','dd-MON-YYYY'),salary, 0) SEP, 
               decode(trunc(EMP_month,'MONTH'),to_date('01-OCT-2006','dd-MON-YYYY'),salary, 0) OCT,
               decode(trunc(EMP_month,'MONTH'),to_date('01-NOV-2006','dd-MON-YYYY'),salary, 0) NOV,
               decode(trunc(EMP_month,'MONTH'),to_date('01-DEC-2006','dd-MON-YYYY'),salary, 0) DEC
          from employee_salary) b
GROUP BY b.EMP_ID

EMP_ID	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
20	0	0	0	0	0	0	0	0	55	50	0	0
21	0	0	0	0	0	0	0	50	0	0	0	0

[Updated on: Wed, 15 November 2006 06:42]

Report message to a moderator

Re: Complex query [message #203699 is a reply to message #203486] Thu, 16 November 2006 01:31 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
nice answer...
Re: Complex query [message #203992 is a reply to message #203699] Fri, 17 November 2006 04:27 Go to previous message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
one similar to above one
is here ..

SELECT EMP_ID,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'JAN', SALARY)) JAN,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'FEB', SALARY)) FEB,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'MAR', SALARY)) MAR,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'APR', SALARY)) APR,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'MAY', SALARY)) MAY,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'JUN', SALARY)) JUN,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'JUL', SALARY)) JUL,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'AUG', SALARY)) AUG,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'SEP', SALARY)) SEP,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'OCT', SALARY)) OCT,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'NOV', SALARY)) NOV,
SUM (DECODE (TO_CHAR (EMP_MONTH, 'MON'), 'DEC', SALARY)) DEC
FROM EMP_SAL
WHERE TO_CHAR (EMP_MONTH, 'YYYY') = '2006'
GROUP BY EMP_ID ;

note: EMP_MONTH column is in date format.
Previous Topic: How to find object dependency in oracle
Next Topic: how to stop a running script but not exit from sqlpus
Goto Forum:
  


Current Time: Sat Dec 10 16:55:25 CST 2016

Total time taken to generate the page: 0.11314 seconds