| SQL PLUS query result in Horizantal way [message #318864] |
Thu, 08 May 2008 05:14  |
NIckman
Messages: 64
Registered: May 2007
|
Member |
|
|
I am running SQL query through SQl plus.
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
select 'JAN1ST, JAN2ND, JAN3RD,JAN4TH,JAN 5TH) FROM DUAL;
select count(emp_id)
from
emptab
where
start_date >=(2008-01-01)
and end_date <= (2008-01-02);
select count(emp_id)
from
emptab
where
start_date >=(2008-01-02)
and end_date <= (2008-01-03);
select count(emp_id)
from
emptab
where
start_date >=(2008-01-03)
and end_date <= (2008-01-04);
select count(emp_id)
from
emptab
where
start_date >=(2008-01-04)
and end_date <= (2008-01-05);
select count(emp_id)
from
emptab
where
start_date >=(2008-01-01)
and end_date <= (2008-01-02);
SPOOL OFF
EndOfCat
I get the output like this in excel.
JAN1ST: 800
JAN 2ND: 588
JAN 3RD: 478
JAN 4TH:123
JAN 5TH: 789.
but, i need the output horizantal way look.format like this in excel.how can i get this?
thanks.N.
JAN 1ST JAN 2ND JAN 3RD JAN 4TH JAN 5TH
800 588 478 123 789
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL PLUS query result in Horizantal way [message #318891 is a reply to message #318882] |
Thu, 08 May 2008 06:57  |
elaiyavel
Messages: 114
Registered: April 2008
|
Senior Member |
|
|
Modify the below script to match your requirement !
select sum(decode(substr(insert_dt,0,1),1,mrkt_key)) as jan,
sum(decode(substr(insert_dt,0,1),2,mrkt_key)) as feb
from key_mrkt;
|
|
|
|
|
|
| Re: SQL PLUS query result in Horizantal way [message #319008 is a reply to message #318891] |
Thu, 08 May 2008 12:23 |
NIckman
Messages: 64
Registered: May 2007
|
Member |
|
|
Hi elaiyavel
select sum(decode(substr(insert_dt,0,1),1,mrkt_key)) as jan,
sum(decode(substr(insert_dt,0,1),2,mrkt_key)) as feb
from key_mrkt;
i did not understand why use substring.
i think decode will be used for change value in a column to different one.
i am running 5 sql squeris to get the count on each day.
my actual query is like this
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');
i run above query with change the variables for start date and end date in the script.
still i did not get how result will get into horizontal view.
with using sum,decode, substr.
i am looking for only count of empsatus.i use oracle 9i.
i looked pivot. it works only 11g.
pl help.
thx,N.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
