-
Oracle Select Query Result In Horizontal Format
HI
I am running SQL query in sql plus using script.
THE query is get count for jan 1st,2,3,4,5 from table.
I need output for each day for 5 days.
my query
is
SPOOL /test/emprpt.csv
but, i need the output horizantal way look to format like this in excel.
that is why using csv file to automatically format in excel
.how can i get this?
thanks.N.
I get the output like this in excel.
select 'jan1st' from dual;
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
select 'jan2nd' from dual;
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
and so on... for all 5 queries.
output will be like this in excel.
JAN1ST: 800
JAN 2ND: 588
JAN 3RD: 478
JAN 4TH:123
JAN 5TH: 789.
but, i want output like this way in excel in horizontal way.
JAN 1ST JAN 2ND JAN 3RD JAN 4TH JAN 5TH
800 588 478 123 789
SPOOL /test/emprpt.csv
select 'jJAN 1ST, JAN 2ND, JAN3RD, JAN 4TH, JAN 5TH' FROM DUAL;
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
select count(empstatus)
from emp,dept
where emp_id = dept_id
and emptype = 'finance'
and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
SPOOL OFF
EndOfCat
But how can i get sql query result in horizontal way.
i run above query with change the variables for start date and end date in the script for jan1st,2,3,4,5.
i looked pivot. i did not understand. how to write this to get the result
in horizontal way.
pl help.
thx,N.
-
Code:
SQL> select * from test_date;
EMPID EMP_TIME EMPSTATUS
---------- --------- ----------
1 01-JAN-08 800
1 02-JAN-08 588
1 03-JAN-08 478
1 04-JAN-08 123
1 05-JAN-08 789
2 01-JAN-08 111
2 02-JAN-08 222
2 03-JAN-08 555
2 04-JAN-08 333
2 05-JAN-08 444
10 rows selected.
SQL> select empid
2 , max(decode(emp_time, '01-JAN-08', empstatus)) "JAN 1ST"
3 , max(decode(emp_time, '02-JAN-08', empstatus)) "JAN 2ND"
4 , max(decode(emp_time, '03-JAN-08', empstatus)) "JAN 3RD"
5 , max(decode(emp_time, '04-JAN-08', empstatus)) "JAN 4TH"
6 , max(decode(emp_time, '05-JAN-08', empstatus)) "JAN 5TH"
7 from (select * from test_date)
8 group by empid;
EMPID JAN 1ST JAN 2ND JAN 3RD JAN 4TH JAN 5TH
---------- ---------- ---------- ---------- ---------- ----------
1 800 588 478 123 789
2 111 222 555 333 444
-
ebrian,
Thanks for your reply.I did n't get it.
I am creating csv file to automatically format into excel.pl see bellow the sql query. the query wroks fine.
i got the result. But in excel it shows each select count value
775 888 999 234 546 in one excel cell.
In order to separate each count result in each separate cell in excel.
i need extra comma(,) after each count to automatically format in excel.
How do add extra comma in this sql query after each select statement.
select
(select count(name)
from emp,dept
where emp_id = dept_id
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate1# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate1# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as a,
(select count(name)
from ah,oh
where ah_oh_idnr = oh_idnr
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate2# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate2# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as b,
(select count(name)
from ah,oh
where ah_oh_idnr = oh_idnr
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate3# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate3# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as c,
(select count(name)
from ah,oh
where ah_oh_idnr = oh_idnr
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate4# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate4# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as d,
(select count(name)
from ah,oh
where ah_oh_idnr = oh_idnr
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate5# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate5# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as e,
(select count(name)
from ah,oh
where ah_oh_idnr = oh_idnr
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate6# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate6# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as f,
(select count(name)
from ah,oh
where ah_oh_idnr = oh_idnr
and emp_type = 'FINS'
and emp_TimeStamp2 >= TO_DATE('&startdate7# 00:00:01','YYYY-MM-DD HH24:MI:SS')
and emp_TimeStamp3 <= TO_DATE('&enddate7# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as g,
from dual;
SPOOL OFF
EndOfCat
-
Thanks all.
I got it.
thx,n.
-
help please
hi, this instance multiple tables (for example, 3 tables) how-to.
SELECT C_TABLE.ID, C_TABLE.NAME, A_TABLE.ID, B_TABLE.SESSION , SUM(B_TABLE.DEPT_TOTAL), SUM(B_TABLE.ARREARS_TOTAL)
FROM A_TABLE , B_TABLE , C_TABLE
WHERE B_TABLE.A_TABLE_ID = A_TABLE.ID
AND B_TABLE.C_TABLE_ID = C_TABLE.ID
AND B_TABLE.C_TABLE_ID = A_TABLE.C_TABLE_ID
AND A_TABLE.CLOSED = ‘O’
AND B_TABLE.YEAR = ‘2012’
AND C.TABLE.ID = ‘2277’
GROUP BY C_TABLE.ID, C_TABLE.NAME, A_TABLE.ID, B_TABLE.SESSION
ORDER BY B_TABLE.SESSION
;
C_TABLE.ID C_TABLE.NAME A_TABLE.ID B_TABLE.SESSION B_TABLE.DEPT_TOTAL B_TABLE.ARREARS_TOTAL
2277 MR.J**** 7222 1 45,24 0
2277 MR.J**** 7222 2 45,24 0
2277 MR.J**** 7222 3 45,24 0
2277 MR.J**** 7222 4 45,24 45,24
2277 MR.J**** 7222 5 45,24 45,24
2277 MR.J**** 7222 6 59,54 59,54
I want to report
B_TABLE.SESSION
C_TABLE.ID C_TABLE.NAME A_TABLE.ID 1 2 3 4 5 6
2277 MR.J**** 7222 45,24 0 45,24 0 45,24 0 45,24 45,24 45,24 45,24 59,54 59,54
thanks....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|