nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE multiple row values in one row

KashSidd

I am trying to find out a solution where I can get the next three values of a column and add them in another field within the same row. So I found the LISTAGG function but its not working for me. Here is the function that I am using:

CAST(EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',"Measures"."Strm",', ',) as char(16))

This is what I get:

I dont know how I would be able to get the next three "STRM" values to show up as follows:

BTW, the "Strm" will be entered through a report prompt so lets say if the User inputs "2336" then I will be needing the next two terms which in this case will be 2354 and 2366. I was thinking to use Presentation variables to pass the prompt values to the report.

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

You want the values from different rows to become distinct columns?

Well the most intelligent solution is to ETL this data into the correct form because otherwise this will be a very heavy operation that gets executed all the time.

KashSidd

The values are from the same column and this is how it looks in a table:

Srinivas Malyala-Oracle

May be try this. First get a unique rank by row_number db function then do a pivot view. I have used different sample set Start Day Name (in your case emplid) ,Start Date (Strm)

In pivot view pull Strm as measure and set aggregation rule as Min (or Max).

KashSidd

Thank you and it works

It looks like:

Last thing I was looking for is to get the First 3 STRM's and also have to remove Nulls. I was trying out the LISTAGG but it doesnt work well with ROW_NUMBER already there... Please let me know should you have some solution?

Srinivas Malyala-Oracle

If you are looking for only 3 then you need to filter on column which has row number (row_number <=3)

KashSidd

I tried but it doesnt work and here is the filter:

I get the following error:

ORA-30483: window functions are not allowed here at OCI call OCIStmtExecute. (HY000)

[Deleted User]

Basically why I didn't participate in the thread anymore and will just re-iterate my statement: this best goes into the physical design.