-
Hello there,
can any one tell me whether we can generate a matrix report in oracle. I have some values in a table and I want a report in which these (rows) values will appear as columns
Here is the table structure.
Name Null? Type
------------------------------- -------- ----
LOOMNO VARCHAR2(10)
SHIFTNO VARCHAR2(10)
DATES DATE
REASON NUMBER(10)
TIMES NUMBER(10)
MM NUMBER(2)
YYYY NUMBER(4)
PERSON_MODIFIED VARCHAR2(50)
DATE_MODIFIED DATE
I want a report like :
loom no Reason1 reason2 reaon3
1 ******
2
3
----------------------------------------
in the above report reason1,2,3 are the values stored in column 'reason' and '******' is the sum of times for each reason value i.e. for reason1,2,3 etc.
thanks in advance
regards,
Anand
Anand Halkunde
-
I think you need to look at function called decode.This will
do matrix kind of report.
-
i did try very hard for an answer to your question if there are only three reasons only then we can do a matrix report in oracle or for that matter any dataabase using decode statement else it is not possible to do.( or perhaphs some brave soul here should attempt the feat)
-
I guess we can generate the matrix report with DECODE even if the fields needed are more than 3
-
manas could you show it to me then coz if it is some definite number then its easy we can do something like
select loomno,decode (reason1 ,nvl(reason1,0),value..................................)
group by blah
but with thousands of reasons one has to go on writing reason 1........................reason 1000
if you have a better way why not post the solution
-
Hi !
I think you can do it if you know the maximum number of rows. I tried an example giving 1 to a maximum of 5 cols.
(There may be a more elegant way, but it works.)
Commit;
6502
----- cut here ----------------
select decode(anzahl.zahl
,1, sg1.id
,2, sg1.id
,3, sg1.id
,4, sg1.id
,5, sg1.id
,NULL
) sg1
, decode(anzahl.zahl
,2, sg2.id
,3, sg2.id
,4, sg2.id
,5, sg2.id
,NULL
) sg2
, decode(anzahl.zahl
,3, sg3.id
,4, sg3.id
,5, sg3.id
,999999
) sg3
, decode(anzahl.zahl
,4, sg4.id
,5, sg4.id
,NULL
) sg4
, decode(anzahl.zahl
,5, sg5.id
,NULL
) sg5
, anzahl.zahl
from (select * from servicegroup where is_top='Y') sg1
, (select * from servicegroup where is_top='Y') sg2
, (select * from servicegroup where is_top='Y') sg3
, (select * from servicegroup where is_top='Y') sg4
, (select * from servicegroup where is_top='Y') sg5
, (select count(*) zahl from servicegroup where is_top = 'Y') anzahl
where (sg1.id < sg2.id or anzahl.zahl < 2)
and (sg2.id < sg3.id or anzahl.zahl < 3)
and (sg3.id < sg4.id or anzahl.zahl < 4)
and (sg4.id < sg5.id or anzahl.zahl < 5)
----- cut here ----------------
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
|