Home » SQL & PL/SQL » SQL & PL/SQL » SQL PLUS query result in Horizantal way
SQL PLUS query result in Horizantal way [message #318864] Thu, 08 May 2008 05:14 Go to next message
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 #318866 is a reply to message #318864] Thu, 08 May 2008 05:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hi, this is known as a pivot query. I'm sure you'll find tons of examples here on OraFAQ

See also: OraFAQ Wiki


MHE
Re: SQL PLUS query result in Horizantal way [message #318875 is a reply to message #318864] Thu, 08 May 2008 06:01 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Use Sum with decode, it will give you the result as you expected.

-- Elaiyavel.
Re: SQL PLUS query result in Horizantal way [message #318882 is a reply to message #318875] Thu, 08 May 2008 06:32 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi Elaiyavel,
can you give me examples using sum and decode to get the result like that.
thx. N.
Re: SQL PLUS query result in Horizantal way [message #318885 is a reply to message #318882] Thu, 08 May 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can you give me examples using sum and decode to get the result like that.

Just search for "pivot" there are plenty of examples.
Don't you want to work a little bit?

Regards
Michel
Re: SQL PLUS query result in Horizantal way [message #318891 is a reply to message #318882] Thu, 08 May 2008 06:57 Go to previous messageGo to next message
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 #318918 is a reply to message #318864] Thu, 08 May 2008 07:53 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
NIckman wrote on Thu, 08 May 2008 06:14

start_date >=(2008-01-01)
and end_date <= (2008-01-02);



There is no possible way that this can work.
Re: SQL PLUS query result in Horizantal way [message #319008 is a reply to message #318891] Thu, 08 May 2008 12:23 Go to previous messageGo to next message
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.







Re: SQL PLUS query result in Horizantal way [message #319009 is a reply to message #319008] Thu, 08 May 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search again. We are not talking about "pivot" from 11g.
This has been asked and answered many and many times and works in ALL versions.

You don't need substr.

Regards
Michel
Re: SQL PLUS query result in Horizantal way [message #319014 is a reply to message #318864] Thu, 08 May 2008 13:33 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
try this:

Kevin Meade's OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Kevin
Previous Topic: equal , not equal , nul in same query
Next Topic: delete multiple tables
Goto Forum:
  


Current Time: Tue Dec 06 14:20:36 CST 2016

Total time taken to generate the page: 0.09105 seconds