can we generate a matrix report in oracle?

[halkunde]

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

[M_Praveen]

I think you need to look at function called decode.This will
do matrix kind of report.

[hrishy]

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)

[manas]

I guess we can generate the matrix report with DECODE even if the fields needed are more than 3

[hrishy]

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

[6502]

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