Solved

How to group 1 to many into just 1 record?

Posted on 2012-03-23
9
350 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

777 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