Pivot Table

[IanWaterman]

I have a view which currently lists up to 5 drivers on a policy, with 18 attributes in columns.

I am now using Crystal to produce a cross tab of this view with atributes now being rows and drivers as column header. I can do this in Crystal with an incredibly fiddly manual cross tab. In MS SQL server there is a function called PIVOT which will effectively do this for me making reporting easier.

I have searched this forum and the only solutions I can see involve Decode or Case. I can not use these as I do not know the number of drivers on each policy, it can vary from 1-5.

Is there an equivalent function to PIVOT in Oracle 10?

Thanks in advance

Ian

 

[taupirho]

..." I have searched this forum and the only solutions I can see involve Decode or Case. I can not use these as I do not know the number of drivers on each policy, it can vary from 1-5 " ...

What is important when using max/decode when pivotting is not that you don't know how many drivers on each policy but that you do know there is a max of 5. So any less than 5 will just have null values in them. I think the use of MAX/decode is perfectly acceptable on this occasion providing performance is not an issue. There are other methods too that can be used (see my FAQ on the Oracle9 board for more details on at 2 other methods to get what you want)




In order to understand recursion, you must first understand recursion.

 

[IanWaterman]

Read your FAQ, I may have misunderstood but I don't think it helps solve my particular issue.

I have data

Driver1, Surname, Name, DOB, Gender, etc 18 columns
Driver2, Surname, Name, DOB, Gender, etc 18 columns
Upto 5 drivers

I need to pivot so that data can be read

Driver1 Driver1
Surname
Name
DOB
GENDER
.
.
18 rows

Your example requires that the attributes all be in a single field and then you group on that field.

Or have a missed an important point?

Ian

 

[Dagon]

With your problem, you're looking to first convert columns to rows (changing surname, DOB etc into rows) and then pivot the other way, converting some of the rows back into columns.

The usual method for changing columns to rows is to use UNION, but I've tried to come up with something better. Here's my proposed solution - I don't know if it's 100% what you want.

drop table drivers;
 
create table drivers (driver varchar2(20), surname varchar2(20), DOB date, gender varchar2(1));
 
insert into drivers values ('DRIVER1', 'Pickles', '24-jan-1940', 'M');
insert into drivers values ('DRIVER2', 'Cameron', '24-jan-1960', 'M');
insert into drivers values ('DRIVER3', 'Clegg', '24-jan-1965', 'M');
 
select driver1_value,
       driver2_value,
       driver3_value
from
(
 select data_order,
          MAX(case when rn=1 then data_element end) as driver1_value,
          MAX(case when rn=2 then data_element end)  as driver2_value,
          MAX(case when rn=3 then data_element end)  as driver3_value
 from
 (select driver, data_element, data_order, dense_rank() over (order by driver) as rn
 from
 (select d.driver, substr(d.data_string, instr(d.data_string,  '|', 1, a.pos)+1, instr(d.data_string, '|', 1, a.pos+1)- instr(d.data_string, '|', 1, a.pos)-1) as data_element, pos as data_order 
 from 
 (select driver, '|'||surname||'|'||dob||'|'||gender||'|' as data_string from drivers) d,
 (select level as pos
 from dual
 connect by level<=3) a))
 group by data_order)
 order by data_order

DRIVER1_VALUE DRIVER2_VALUE DRIVER3_VALUE
------------- ------------- -------------
Pickles       Cameron       Clegg        
24-JAN-40     24-JAN-60     24-JAN-65    
M             M             M

For Oracle-related work, contact me through Linked-In.

 

[IanWaterman]

Thanks for the tips, tried Dagon's solution but it was too slow to exceute. Effectivley having to perform that query on a result of a view. Would need to convert everything to a Stored Procedure to improve performnce.

So took the cowards option and did all the reformatting in Crystal Report.

Ian