# Combine query results into 1 record

**URL:** <https://forums.toadworld.com/t/combine-query-results-into-1-record/28045>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [December 16, 2013, 12:19pm UTC](https://forums.toadworld.com/t/combine-query-results-into-1-record/28045 "2013-12-16T12:19:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ricky.a.mercado](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/ricky.a.mercado/32/214_2.png) [@ricky.a.mercado](https://forums.toadworld.com/u/ricky.a.mercado)\
**Post date:** [December 16, 2013, 12:19pm UTC](https://forums.toadworld.com/t/combine-query-results-into-1-record/28045/1 "2013-12-16T12:19:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rjesse](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/rjesse/32/156_2.png) [@rjesse](https://forums.toadworld.com/u/rjesse)\
**Post date:** [December 16, 2013, 12:28pm UTC](https://forums.toadworld.com/t/combine-query-results-into-1-record/28045/2 "2013-12-16T12:28:39Z")

</div>

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](http://www.toadworld.com/platforms/oracle/f/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

---

<div class="post-metadata">

**Author:** ![ricky.a.mercado](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/ricky.a.mercado/32/214_2.png) [@ricky.a.mercado](https://forums.toadworld.com/u/ricky.a.mercado)\
**Post date:** [December 16, 2013, 12:44pm UTC](https://forums.toadworld.com/t/combine-query-results-into-1-record/28045/3 "2013-12-16T12:44:47Z")

</div>

My apologies and thanks for the redirect.

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [December 16, 2013, 1:29pm UTC](https://forums.toadworld.com/t/combine-query-results-into-1-record/28045/4 "2013-12-16T13:29:44Z")

</div>

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 🙂 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;

* * *

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [December 17, 2013, 12:22pm UTC](https://forums.toadworld.com/t/combine-query-results-into-1-record/28045/5 "2013-12-17T12:22:59Z")

</div>

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