How do I roll up a resultset? 2

[shauntwo]

All,

I'm querying a table that, for the sake of discussion, has the following data:

MODEL DESC_LINE DESCRIPTION
***** ********* ***********
X 1 Super high-efficiency
X 2 unit with widgets and
X 3 gizmos.
Y 1 Medium-efficiency unit
Y 2 with some gadgets.

As you can see, the model descriptions are broken out by record - this was done for restrictions dictated by an application. However, I'd like to be able to query each model and a "rolled up" or concatenated description within a single field, such that the above data, when queried, would give me two records - one for X, and one for Y, with the full description in one field for each model. There's a varying number of description lines, so I can't anticipate them in advance with a series of subqueried fields in my SELECT. I'm kind of an intermediate end-user of Oracle, so I don't have the ability to write functions in PL/SQL to tackle this - only pass-through queries in Access. Is there an Oracle function of some sort that would do this efficiently? DECODE and CASE don't strike me as viable solutions.

Thanks to all for your assistance.

Shaun

 

[AOLEURO]

Hi Shaun

I hope this code can be used through Microsoft Access, but you have to try.


First I create a table like this:

 create table aol_euro( model varchar2(1) not null, 
                       desc_line integer not null, 
                       description varchar2(30)
                      ) tablespace table_data;

I now insert some rows like this:

insert into aol_euro values( 'X', 3, 'gizmos.');

insert into aol_euro values( 'X', 1, 'Super high-efficiency' );

insert into aol_euro values( 'X', 2, 'unit with widgets and');

insert into aol_euro values( 'Y', 2, 'with some gadgets.' );

insert into aol_euro values( 'Y', 1, 'Medium-efficiency unit' );

And if I query all rows, then the output is:

select * from aol_euro;

M  DESC_LINE DESCRIPTION
- ---------- ------------------------------
X          3 gizmos.
X          1 Super high-efficiency
X          2 unit with widgets and
Y          2 with some gadgets.
Y          1 Medium-efficiency unit

Note that I inserted rows NOT in correct line order, so I can test query sorting.

And now I make the query:

select a1.model, 
       trim(a1.description||' '||a2.description||' '||a3.description||' '||a4.description||' '||a5.description) description
from aol_euro a1, aol_euro a2, aol_euro a3, aol_euro a4, aol_euro a5
where a1.desc_line = 1
and   a1.model = a2.model

(+)

and   2        = a2.desc_line

(+)

and   a1.model = a3.model

(+)

and   3        = a3.desc_line

(+)

and   a1.model = a4.model

(+)

and   4        = a4.desc_line

(+)

and   a1.model = a5.model

(+)

and   5        = a5.desc_line

(+)

group by a1.model, trim(a1.description||' '||a2.description||' '||a3.description||' '||a4.description||' '||a5.description);

Note that I have made it for max. 5 lines – you need to extend the statement if more than 5 lines can be joined.

The lines

(+)

tells that maybe there are no rows – so ‘insert’ NULL in columns.

The output is:

 M DESCRIPTION
- ----------------------------------------------------
X Super high-efficiency unit with widgets and gizmos.
Y Medium-efficiency unit with some gadgets.

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[shauntwo]

Allan,

I haven't had a chance yet to implement your suggestion, but your solution is pretty slick, and you've taught me a couple of things I wasn't familiar with in Orace - aliasing the same table multiple times, and using TRIM, which I surmise is the same function as that in Office products. A star for you is definitely in order, and thanks for your help.

Shaun