You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register
Get Started: AI Resources for Oracle Cloud HCM – Go Here

Progress with Redwood: Redwood Resources for Oracle Cloud HCM -  Go Here

BIP Report: Converting multiple row & multiple column values into single row and multiple column val

edited Feb 17, 2020 6:49AM in Reporting and Analytics for HCM 1 comment

Summary

BIP Report: Converting multiple row & multiple column values into single row and multiple column value

Content

Dear Experts

I am working on a BIP report (SQL query based) on which I need to extract data as per the below requirement. 

Sample Back end Table data:

Person Number Content Type Id Designation Email Address Department Work Location Mailing Address
12345 1101 Delivery Manager        
12345 1102   testemail@gmail.com      
12345 1103     A00    
12345 1104       HLL-E1  
12345 1105         Home Address
45678 1101 Senior Consultant        
45678 1103     A12    
45678 1105         Work Address


Required output: 

Person Number Designation Email Address Department Work Location Mailing Address
12345 Delivery Manager testemail@gmail.com A00 HLL-E1 Home Address
45678 Senior Consultant   A12   Work Address

I have tried PIVOT function but couldn't achieve as the values are split across different columns. since actual data volume is huge more than 100,000 records, I am trying to to extract data in desired format within data model query instead of etext template. 

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!