CODEWITHSUNDEEP
sqloracle-database
user8595948
user8595948

Reputation:

Oracle query - merging multiple results in a single row

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?

Upvotes: 0

Views: 50

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

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

Upvotes: 1

Related Questions