Combine query results into 1 record

Toad for Oracle
1

Hello! I am new to this community and this is my first question posted in the forum/community.

I’m working on a query that will return multiple values for a record into 1 record. Here’s the situation:
Table 1

Operation ID

Description Update ID

1

801

1

802

1

804

2

78

2

80

Table 2

ID

User ID

Char Location

Length

Inserted Text

Date

801

A763X

302

-1

ABC

12/10/2013

802

A763X

459

-1

DEF

12/10/2013

804

A763X

576

-1

123

12/10/2013

78

B43FC

87

-1

Here

10/6/2013

80

B43FC

100

-1

There

10/6/2013

As one can see, the link between the 2 tables above is Description Update ID = ID.

Select Inserted Text
From table 2
where id in (select Description Update ID
from table 1
where Operation ID = 1)

The above query will return 3 records (ABC DEF GHI) as expected. What I am trying to get is a query that will return 1 record with all the data from that field. So if I wanted results for the same Operation ID, I would like it displayed as below:

Operation ID

Inserted Text

1

ABC, DEF, 123

For some background, the particular type of operation I’m trying to report on for the users has 3 fields that must be filled with data. Even though table 1 does not show a third record for Operation ID = 2, each of the records will have these 3 fields that must be filled in. I would like these fields to be mapped into one record. The query above is actually part of a much larger query that is already pulling information from other tables. As an alternative or even better solution, can the 3 values be displayed on 1 records as 3 separate columns??

Thanks to all for their help and assistance in solving this.

2

Hi Tavi,

Welcome! And grats on your first post!

However, your question is about generic SQL and not Toad-specific, so you’d have better luck posting at the Oracle forums at www.toadworld.com/…/53.aspx if Oracle is the database you’re using.

In any case, if you’re using Oracle v11gR2+, check out the LISTAGG function in the Oracle docs. Otherwise, google “STRAGG” on the Ask Tom website at Oracle.

Good luck!

Rich

3

My apologies and thanks for the redirect.

4

Tavi - Like Rich J said, this would probably be better offered up on the Oracle forums, as there is probably a query for that. However, for giggles, and an attempt to reach out to my pl/sql roots, I thru together a little script that will do what you want. I’m sure your looking for an actual query, but sometimes writing a simple script can be just as fast and imo more fun :slight_smile: Replace the table names with yours, and run as a script - the results are sent to dbms_output.

declare

l_record varchar2(1000) := null;

l_prev_op_id number := 0;

l_counter number := 0;

cursor c_get_data is

select operation_id c_op_id,

inserted_text c_inserted_text

from table1 a,

table1x b

where a.description_update_id = b.id

group by operation_id, inserted_text

order by operation_id;

begin

for cur_rec in c_get_data loop

begin

l_counter := l_counter + 1;

if l_counter = 1 then

l_record := cur_rec.c_inserted_text;

else

if cur_rec.c_op_id = l_prev_op_id then

l_record := l_record ||’,’||cur_rec.c_inserted_text;

else

dbms_output.put_line(l_prev_op_id ||’ '||l_record);

l_record := cur_rec.c_inserted_text;

end if;

end if;

l_prev_op_id := cur_rec.c_op_id;

end;

end loop;

dbms_output.put_line(l_prev_op_id ||’ '||l_record);

end;

5

This might help (if you are on Oracle 11R2):

SELECT operation_ID, LISTAGG(Inserted_Text, ‘,’) WITHIN GROUP (ORDER BY operation_ID) AS xxx

FROM table2

GROUP BY operation_ID;

Brg,

Damir