Home » SQL & PL/SQL » SQL & PL/SQL » Complex query
Complex query [message #203486] |
Wed, 15 November 2006 04:52  |
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   |
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 #203992 is a reply to message #203699] |
Fri, 17 November 2006 04:27  |
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.
|
|
|
Goto Forum:
Current Time: Wed Feb 19 00:12:03 CST 2025
|