Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to group 1 to many into just 1 record?

Posted on 2012-03-23
9
Medium Priority
?
367 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 74

Accepted Solution

by:
sdstuber earned 2000 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 74

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 74

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

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 49

Expert Comment

by:Dale Fye
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 74

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 74

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

877 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