How to group 1 to many into just 1 record?

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
LVL 1
thamilto0410Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
sdstuberCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
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
Dale FyeCommented:
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
sdstuberCommented:
>>>> Not sure how to do the UDF in SQL or Oracle.

see the article referenced above
0
thamilto0410Author Commented:
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
thamilto0410Author Commented:
The listagg got the job done and I figured out the rest myself.  Thanks.
0
sdstuberCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.