0

I do have the following query which displays two results.

Query:

select /*+ parallel(16) */ * from CONTRACT where CONTRACT_ID ='1234';

Result:

_____________________________________________________________________________________ |CONTRACT_SOURCE | CONTRACT_ID | ROLE | ROLE_ID | STD_CD | INDEX _____________________________________________________________________________________ |Source | 1234 | role_driver | unique1 | LOAD | 9 |Source | 1234 | role_insured| unique2 | LOAD | 9 _____________________________________________________________________________________

I would like to fetch these results merged to in the below format.

_____________________________________________________________________________________________________________________ |CONTRACT_SOURCE | CONTRACT_ID | ROLE | ROLE_ID | ROLE | ROLE_ID | STD_CD | INDEX | _____________________________________________________________________________________________________________________ |Source | 1234 | role_driver | unique1 | role_insured | unique2 | LOAD | 9 | _____________________________________________________________________________________________________________________

Can I achieve this through an Oracle query?

Improve this question
2
  • Will there be only two roles per contract_id? Commented Sep 20, 2017 at 9:43
  • Yes @GurV ! Exactly Commented Sep 20, 2017 at 9:52

1 Answer 1

Reset to default
1

You can use row_number and aggregation to get the required multi column pivoting:

select contract_source, contract_id, std_cd, in, max(case when rn = 1 then role end) as role_1, max(case when rn = 1 then role_id end) as role_id_1, max(case when rn = 2 then role end) as role_2, max(case when rn = 2 then role_id end) as role_id_2 from ( select c.*, row_number() over ( partition by contract_source, contract_id, std_cd, in order by role_id ) as rn from contract c ) t group by contract_source, contract_id, std_cd, in
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks a lot @GurV !