Combine record results into one records

Toad for Oracle
1

This was originally posted into the Toad for Oracle community, but I was redirected here as I was told this was a more general SQL question:

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 123) 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, this 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 record as 3 separate columns??

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

2

Hello Tavi,

please see docs.oracle.com/…/functions089.htm for

Operation ID
Inserted Text
1
ABC, DEF, 123
and http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55389

for

Operation ID
Text1
Text2
Text3
1
ABC
DEF
123
PIVOT will be much more complicated, LISTAGG is just a GROUP BY-Aggregate function.

Best regards,

Blama

3

Ironic that you suggested the LISTAGG function as I was using it from a response I got back from the Toad for Oracle community (from the original post).
After using that and the links you mentioned above (along with some additional research) I was able to come up with the following SQL:

select table1.operation_id,
listagg(table2.inserted_text, chr(10))
within group (order by char_location) “Data”,
listagg(table2.date, chr(10))
within group (order by date) “Data Date”
from table2
join table1 on table1.description_update_id = table2.id
where table1.operation_id = 1
group by table1.operation_id

Which gave me results looking as below:

Operation ID
Data
Data Date
1
ABC
DEF
123
12/10/2013
12/10/2013
12/10/2013
I was then able to take this and incorporate it into the much larger SQL that I had. Thanks again for the help.