Oracle Select Query Result In Horizontal Format

[NIckman]

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.

[ebrian]

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

[NIckman]

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

[NIckman]

Thanks all.
I got it.
thx,n.

[hyilmaz]

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....