Solved

How to group 1 to many into just 1 record?

Posted on 2012-03-23
9
346 Views
Last Modified: 2012-03-26
Need a little help I have a query similary to :

select id, analyst, case, concat(comments + x.comments), rating from table1, (select id, analyst comments from table2) x where x.id=table1.id and  table1.date_stamp between date 1 and date 2 .  The query is working I am just missing one little thing.

Table 1  will only have 1 record per analyst
Table 2 could have multiple records that match up to that one record in table 1.  How do I group all of the summaries out of table 2 to be shown as one summary to match the 1 record in table 1?

So for now my report looks like

Analyst   Case     Summary
TSMITH   11112    something
TSMITH   11112    something else

AND I WANT

TSMITH  11112      something something else

Can someone send me a link to a similar example that I can follow and use to fix what I am missing. Thanks.
                             
T
0
Comment
Question by:thamilto0410
  • 6
  • 2
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37758132
if using 11gR2 then try this...


select analyst, case, listagg(summary, ' ') within group (order by some_column)
from your_table
group by analyst, case

change "some_column" to whatever other columns you have in your table that would determine which summary values come before or after others.

If you don't care, just use the value 1
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37758141
if you don't have 11gR2 but do have 10g or higher then try this...


select analyst, case, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", summary|| ',') order by some_column), '/x/text()').getstringval(),',')
from your_table
group by analyst, case


again, set the order by to whatever is appropriate
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37758152
if you don't have 10g but have 9i or higher then try building a string aggregation function

search for "stragg"

or use the concat_agg found here
http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37758156
if you don't have 9i,  then you can't use sql to do this.

You'll have to write pl/sql to iterate through the values and generate the string you need.

If this is the case I highly recommend upgrading
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37758157
If you can create a user defined function, then you could use something like:

SELECT Analyst, Case#, fnConcat([Case#]) as Summary
FROM yourTable

In the UDF, fnConcat, you would run another query that only returns the [Summary] fields associated with the passed [Case#], and then loops through that recordset, concatenating the the various [Summary] values into a single string, which is what would get returned by the function.

Not sure how to do the UDF in SQL or Oracle.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37758160
>>>> Not sure how to do the UDF in SQL or Oracle.

see the article referenced above
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 37758534
sdstuber:  That (LISTAGG) was fantastic works like a charm.  Let me ask one more question and then I will award points.  It seems some of my users have put the same exact comment copy paste into both tables so of course it shows in the results twice.  Is there a way after listagg to group, compare and remove exact dups?  Or do I do another question for this?  Also I just rechecked and I need to tweak a little more the comments are joining great but the record is showing once for table 1 and once for table 2 and usually the comments for the record from table 2 is everything from both tables how do I get it down to just the 1 record from both tables with name, case id, rating, and comments?
0
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 37766490
The listagg got the job done and I figured out the rest myself.  Thanks.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37766511
sorry I missed the previous post.  
I'm not sure what you were asking there anyway.  :)

glad you got it worked out and glad to help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now