Report Builder 2.0 or Oracle string aggregation

Question

In Report Builder 2.0, I'm trying to create a table with data like the following:

Person | StrID's
-------------------
Jim    | a, b, c, d
Mary   | h, k
Sue    | l, m, p, z

The problem is that my Oracle SQL query returns data in the following format:

Person | StrID
--------------
Jim    | a
Jim    | b
Jim    | c
Jim    | d
Mary   | h

And so on. I don't have access in Oracle to use LISTAGG as described in this tutorial, nor can I use EXPLAIN PLAN or tell you which version of Oracle I'm using because I have to access the database through a very limited interface. I've looked into Report Builder's functions like Aggregate but cannot find a way to concatenate multiple string values together with an aggregate function. If I create a table grouping by Person, I get separate rows for each of the StrID's. Using Join(Fields!StrID.Value, ", ") causes "#Error" to show up in the table cell, I assume because Fields!StrID.Value is not actually a multi-value field, it's a single value per Person.

Can anyone tell me a way of showing a list of the associated StrID's by Person in one row, either through SQL or Report Builder 2.0/Visual Basic?

Edit: I can use the LAG function if anyone can think of a creative solution using that. Turns out I can use PARTITION BY in conjunction with the LAG function, too.

Answer 1

Hi,

I have had a look on Tom Kyte's site? If you search for "pivot" the results might be of some help.

e.g.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:124812348063

or

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15151874723724

Unfortunately I don't have access to reports 2.0 (Fortunately(?) I started with 6i!) so I can't confirm that this will work in your environment.

Answer 2

Here http://www.sqlsnippets.com/en/topic-11787.html they have at least 10 methods, some of which do not require creating additional objects. You may try any of those to see if they suit your Oracle version.

Answer 3

After finding out that the LAG function worked, I thought I might be able to get a StrID and the previous row's StrID, concatenate them together, and then somehow keep doing that for all the rows for a Person. I found this AskTom question and was able to adapt it to work with my tables:

SELECT Person, 
       SUBSTR(
         MAX(list) KEEP (DENSE_RANK FIRST ORDER BY lev DESC),
         2
       ) AS StrIDs
FROM ( 
       SELECT Person,
              SYS_CONNECT_BY_PATH(StrID, ', ') AS list,
              LEVEL AS lev
       FROM (
              SELECT Person,
                     StrID,
                     LAG(StrID, 1)
                       OVER (PARTITION BY Person ORDER BY StrID) AS prev_id
              FROM my_table
              WHERE other_conditions = 'blah blah'
            )
       START WITH prev_id IS NULL
       CONNECT BY PRIOR StrID=prev_id
     )
GROUP BY Person
ORDER BY Person

I now get data in the format I expected, namely:

Person StrIDs
jeff   a, b, c
linda  k, l, m