Pivot Type queries

[IanWaterman]

I am working on an insurance report

Driver details table is standard format

Driver, gender, age, claims.... etc

I need to out put as

Driver1 Driver2
Gender Gender
Age Age
Claims Claims
etc...

Each policy can have anywhere between 1 and 20 drivers,

Only need to output data one policy at a time for each document.

Ian

 

[SantaMufasa]

Ian,

Several of us can respond with actual code if you could please post (highly simplified) "CREATE TABLE..." and "INSERT INTO..." statements. Additionally, it would be very helpful to see some formatted output showing what you want for both minimal and maximal rows/columns.

Thanks for helping us to help you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

Converting rows to columns like that is not something raw SQL is particularly good at, so any solution that is just pure SQL is probably going to be a bit convoluted. However, here is one approach that might work:

create table policies (policy_no number, driver varchar2(20), gender varchar2(10),
age number, claims number);


insert into policies values (1, 'Smith', 'M', 40, 5);
insert into policies values (1, 'Jones', 'F', 35, 0);
insert into policies values (1, 'Brown', 'M', 44, 11);
insert into policies values (2, 'Smith', 'M', 40, 5);
insert into policies values (2, 'Williams', 'M', 44, 6);
insert into policies values (2, 'Lawrence', 'F', 21, 2);
insert into policies values (2, 'Johnson', 'M', 88, 1);

WITH res as 
(select policy_no,
        driver,
        gender,
        age,
        claims,
        row_number() over (partition by policy_no order by driver) as rn
from policies
where policy_no=2)
select max(case when rn=1 then driver end) as driver1, 
       max(case when rn=2 then driver end) as driver2, 
       max(case when rn=3 then driver end) as driver3, 
       max(case when rn=4 then driver end) as driver4 
from res
union all
select max(case when rn=1 then gender end), 
       max(case when rn=2 then gender end), 
       max(case when rn=3 then gender end), 
       max(case when rn=4 then gender end) 
from res
union all
select max(case when rn=1 then to_char(age) end), 
       max(case when rn=2 then to_char(age) end), 
       max(case when rn=3 then to_char(age) end), 
       max(case when rn=4 then to_char(age) end) 
from res
union all
select max(case when rn=1 then to_char(claims) end), 
       max(case when rn=2 then to_char(claims) end), 
       max(case when rn=3 then to_char(claims) end), 
       max(case when rn=4 then to_char(claims) end) 
from res
/

Johnson                                  Lawrence                                 Smith                                    Williams
M                                        F                                        M                                        M
88                                       21                                       40                                       44
1                                        2                                        5                                        6

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

 

[IanWaterman]

The result Dagon gives and the Insert commands are close to the table structure.

I will give Dagons query a try, then I have to report on this with Crystal Reports 10.

Ian

 

[Dagon]

It occurred to me that you might need to guarantee that the rows come out in the correct order. You could do this by amending the query as follows:

select col1,
       col2,
       col3,
       col4
from
(
WITH res as 
(select policy_no,
        driver,
        gender,
        age,
        claims,
        row_number() over (partition by policy_no order by driver) as rn
from policies
where policy_no=2)
select 1 as ordno,
       max(case when rn=1 then driver end) as col1, 
       max(case when rn=2 then driver end) as col2, 
       max(case when rn=3 then driver end) as col3, 
       max(case when rn=4 then driver end) as col4 
from res
union all
select 2,
       max(case when rn=1 then gender end), 
       max(case when rn=2 then gender end), 
       max(case when rn=3 then gender end), 
       max(case when rn=4 then gender end) 
from res
union all
select 3, 
       max(case when rn=1 then to_char(age) end), 
       max(case when rn=2 then to_char(age) end), 
       max(case when rn=3 then to_char(age) end), 
       max(case when rn=4 then to_char(age) end) 
from res
union all
select 4,
       max(case when rn=1 then to_char(claims) end), 
       max(case when rn=2 then to_char(claims) end), 
       max(case when rn=3 then to_char(claims) end), 
       max(case when rn=4 then to_char(claims) end) 
from res)
order by ordno

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