OraFAQ Forum: Server Administration » Transpose rows to column

Home » RDBMS Server » Server Administration » Transpose rows to column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Transpose rows to column [message #373960]

Fri, 18 May 2001 03:10

Amber Fatima
Messages: 19
Registered: September 1999

Junior Member

i have a table
desc receipt_detail_t
Name Null? Type
----------------------- -------- ----------------

CREATED_T NUMBER(38)
RECEIPT_ACCOUNT_NO VARCHAR2(20)
RECEIPT_AMOUNT NUMBER
RECEIPT_LOCATION VARCHAR2(30)
RECEIPT_MIDDLE VARCHAR2(30)
RECEIPT_PAYMENT_MODE VARCHAR2(20)
RECEIPT_PACKAGE VARCHAR2(120)
RECEIPT_NO VARCHAR2(25)
, where created_t is in numeric format of date like 945711025 = 20-12-99 10:12:25
i have to produce a sql report like this
date 9:00-9:30 9:30 -10:00 ---- to 23:30 -0:00
16-May-01 sum of reciept_no
17-May-01

this is like a matrix .
cells b/w date and interval have counts of receipts generated ?
any help will be great help.
Amber

Report message to a moderator

Re: Transpose rows to column [message #374000 is a reply to message #373960]

Fri, 18 May 2001 11:36

Sirajdin
Messages: 17
Registered: May 2001

Junior Member

u can use decode function to do this..

could u pls tell me what algorithm u've used to convert that number to the date format u've mentioned..i'll get u the query..

Report message to a moderator

Re: Transpose rows to column [message #374001 is a reply to message #373960]

Fri, 18 May 2001 23:51

Amber Fatima
Messages: 19
Registered: September 1999

Junior Member

i use a function as to convert dates from number :
function cyb_date_converter
(mdatenumber in varchar2) return date
is
begin
return ((mdatenumber + 18000) / 86400) + TO_DATE ('19700101 0000', 'YYYYMMDD HH24MI');
end;
then i create a table test1 as
create table test1
select
decode (substr(receipt_location,1,3),
'loc','Head Office',
'bhd','Bahdurabad',
'gej','Gulistan-e-Jauhar',
'lhr','Lahore',
'nzm','Nazimabad',
'ptr','Park Tower',
'zam','Zamzama',
'ghd','Gulshan-e-Hadeed' ,
'BHD','Bahrudabad',
'GEJ','Gulistan-e-Jauhar',
'LHR','Lahore',
'LOC','Head Office',
'NZM','Nazimabad',
'PTR','Park Tower',
'ZAM','Zamzama',
'GHD','Gulshan-e-Hadeed' ,
'SHD','Shadman Town - Lahore')
) Location ,
to_char(cyb_date_converter(created_t),'dd-mm-yy') daate,
to_char(cyb_date_converter(created_t),'hh24') hr,
to_char(cyb_date_converter(created_t),'mi') min,
to_char(cyb_date_converter(created_t),'ss') sec,
RECEIPT_AMOUNT
from receipt_detail_t
where to_char(cyb_date_converter(created_t),'mm') in ('01','02','03','04')
-- jan , feb , mar , apr
and to_char(cyb_date_converter(created_t),'yy') = '01'
and (UPPER(SUBSTR(RECEIPT_LOCATION,1,3)) in
('BHD','GEJ','LHR','LOC','NZM','PTR','ZAM','GHD') or
UPPER(SUBSTR(RECEIPT_DESC,1,3)) in
('BHD','GEJ','LHR','LOC','NZM','PTR','ZAM','GHD',
'SHD')
AND RECEIPT_PAYMENT_MODE LIKE '%' )
and (upper(receipt_k_code)!='YY' or receipt_k_code is null )
after that i have table as
test1 having : columns as
location ,daate , hr ,min , sec .
after that i have created another table as
create table test2

rem each time created for each branch location
create table test2 as
select location ,daate ,
hr || ':' ||
decode( sign(mins-30),0,'00',-1,'00',1,'30') || '-' ||
hr || ':' ||
decode( sign(mins-30),0,'30',-1,'30',1,'00')
time_period ,
count(receipt_no) total
from test1
group by
location , daate ,
hr || ':' ||
decode( sign(mins-30),0,'00',-1,'00',1,'30') || '-' ||
hr || ':' ||
decode( sign(mins-30),0,'30',-1,'30',1,'00')
------------------------------------
which gives output in this format :
location daate time_period count(receipt_no)
-------- ------ ---------- - -----------------
lahore 13-May-01 9:00-9:30 45
etc.
after that table output in excel using oraexcel :
i have to use pivot table option to transpose rows to columns :
which is my required format of data.
like :
date 9:00-9:30 9:30-10:00 upto 23:30-0:00
13-may-01 45 0 ------------
----------------------------
now i need a single query to do all this stuff without the help of pivot table .
'cause it is every day usage reports i have to create .
thanks .
Amber .

Report message to a moderator

Re: Transpose rows to column [message #374003 is a reply to message #373960]

Sat, 19 May 2001 03:44

Sirajdin
Messages: 17
Registered: May 2001

Junior Member

select location,daate,
decode(time_period,'09:30-10:00',total,null) "09:30-10:00",
decode(time_period,'10:00-10:30',total,null) "10:00-10:30",
decode(time_period,'10:30-11:00',total,null) "10:30-11:00",
decode(time_period,'11:00-11:30',total,null) "11:00-11:30",
decode(time_period,'11:30-12:00',total,null) "11:30-12:00",
decode(time_period,'12:00-12:30',total,null) "12:00-12:30",
decode(time_period,'12:30-13:00',total,null) "12:30-13:00",
decode(time_period,'13:00-13:30',total,null) "13:00-13:30",
decode(time_period,'13:30-14:00',total,null) "13:30-14:00",
decode(time_period,'14:00-14:30',total,null) "14:00-14:30",
decode(time_period,'14:30-15:00',total,null) "14:30-15:00",
decode(time_period,'15:00-15:30',total,null) "15:00-15:30",
decode(time_period,'15:30-16:00',total,null) "15:30-16:00",
decode(time_period,'16:00-16:30',total,null) "16:00-16:30",
decode(time_period,'16:30-17:00',total,null) "16:30-17:00",
decode(time_period,'17:00-17:30',total,null) "17:00-17:30",
decode(time_period,'17:30-18:00',total,null) "17:30-18:00",
decode(time_period,'18:00-18:30',total,null) "18:00-18:30",
decode(time_period,'18:30-19:00',total,null) "18:30-19:00",
decode(time_period,'19:00-19:30',total,null) "19:00-19:30",
decode(time_period,'19:30-20:00',total,null) "19:30-20:00",
decode(time_period,'20:00-20:30',total,null) "20:00-20:30",
decode(time_period,'20:30-21:00',total,null) "20:30-21:00",
decode(time_period,'21:00-21:30',total,null) "21:00-21:30",
decode(time_period,'21:30-22:00',total,null) "21:30-22:00",
decode(time_period,'22:00-22:30',total,null) "22:00-22:30",
decode(time_period,'22:30-23:00',total,null) "22:30-23:00"
from test2

--hope this helps..reply me if it works..curse me if not
--customize to meet ur need..

Report message to a moderator