Columns into Single Row Values

00791a16-2951-43dd-9afb-db8fed6712e9

Hello All!

I am attempting to make a report that has to change column values into a single row value. I am on 11g Release 1 (11.1.1)

(ex:

Name_______Certification____

John Smith      Certification 1

John Smith      Certification 2

John Smith      Certification 3

John Smith      Certification 4

)

Instead I need:

Name_______Certification____

John Smith      Certification 1, Certification 2, Certification 3, Certification 4.

I've seen many posts about using the evaluate function but that is not available due to security restrictions with the NQSconfig file.

Does anyone know other alternatives to produce the above result in an OBIEE analysis.

[Deleted User]

If you have EVALUATE turned off then do it in the ETL or write an opaque view in the RPD physical layer.

Joel

To add some detail to @Christian Berg, you'll have to do this transformation in your ETL process ideally (or in an Opaque view - not ideal though) using some sort of PIVOT function to transpose your rows to columns (you didn't mention your data source). If it's Oracle, then this might be of help - https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

Robert Angel

re: opaque view and some sort of pivot - I think you need listagg; - https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm

Or if you have a very much earlier version of Oracle DB; - https://asktom.oracle.com/pls/asktom/f?p=100:11:::::p11_question_id:229614022562

[Deleted User]

@Joel Acha @Robert Angel  we should make these answers a combined effort to begin with