Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to group 1 to many into just 1 record?

Posted on 2012-03-23
9
Medium Priority
?
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

718 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