Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL - transposing & combining data from duplicate records

Posted on 2013-06-26
8
Medium Priority
?
287 Views
Last Modified: 2013-07-15
Okay here's what I'm trying to do.

I have a table with the columns

customer_id, purchase_agent, product_purchased

When I query it comes out like:

1  agent1  product1, product2
1  agent2  product1

2  agent1  product3
2  agent2  product2, product3



What I want is:
1 agent1  product1, product2, product1
1 agent2  product1, product2, product1
2 agent1  product3, product2, product3
2 agent2  product3, product2, product3


Any thoughts?


What I have right now, thanks to an earlier question.
<cfoutput query="getRows" group="customer_id">
<cfoutput group="purchase_agent">
<tr>
    <td>#customer_id#</td>
    <td>#purchase_agent#</td>
    <td>
	<cfoutput>
<cfif currentRow gt 1 and getRows.customer_id[currentRow] getRows.customer_id[currentRow-1]>,&nbsp;</cfif>
	 #product_purchased#
	</cfoutput>
    </td>
</tr>
</cfoutput>

Open in new window

.
0
Comment
Question by:rrattie
  • 4
  • 4
8 Comments
 
LVL 53

Expert Comment

by:_agx_
ID: 39279767
<cfoutput query="getRows" group="customer_id">
                  <cfoutput group="purchase_agent">
                 ....

[EDIT] Grouped output's only work if the query is sorted the same way you're grouping. My guess is your query results aren't ordered correctly.   Since you're grouping by customer_id, then purchase_agent, your query must be sorted the same way:

           SELECT ...
           FROM   ....
           ORDER BY customer_id,purchase_agent, ... other columns
0
 
LVL 2

Author Comment

by:rrattie
ID: 39279839
<cfquery name="getRows" datasource="#DSN#">
   SELECT    *
   FROM    sales_table
   WHERE  1 = 1
   ORDER BY customer_id, purchase_agent
</cfquery>

Open in new window


Adding that ORDER BY bit didn't change anything other than how the data was displayed (the order).

Still not getting all 'product_purchased' into a single column. It's still separating them based on 'purchase_agent'.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 39279954
What I want is:
1 agent1  product1, product2, product1
1 agent2  product1, product2, product1
2 agent1  product3, product2, product3
2 agent2  product3, product2, product3
EDIT: Hm... on second thought  I'm not sure a grouped output can give you those results.  What does the raw query dump look like?  You seem to be doing more than just grouping the products for a given agent and company...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 2

Author Comment

by:rrattie
ID: 39281314
Yep, the grouping almost got me where I need to be, but of course cannot transpose the products purchased.

So basically like what you quoted above, each row for a customer should have all products purchased in the product_purchased column, not just the ones handled by the individual agent.  

It's a PITA, but it's a reporting requirement that I cannot get around.
0
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 39281838
[EDIT]

> all products purchased

All the products purchased overall - or all products purchased per customer or customer+agent?

You'll probably need to do this in sql.  It would help to see the actual query *and* a sample of the raw data in whatever table(s) are involved.  Feel free to sanitize it. I just need to know the relationships and data to figure out what kind of query is needed .. because I don't think you can get the aggregations needed with CF alone.
0
 
LVL 2

Author Comment

by:rrattie
ID: 39327719
Yes, I did eventually end up solving this with SQL and CF, but I had to make use of  a 'temp' table with some of the data from the sales_table.

sales_table_temp held the customer_id and product_purchased only.

<cfquery name="getRows" datasource="#DSN#">
SELECT * FROM sales_table a
inner join sales_table_temp c
on a.customer_id = c.customer_id
</cfquery>



<cfoutput query="getRows" group="customer_id">
<tr>
    <td>#customer_id#</td>
    <td>#purchase_agent#</td>
    <td>
	<cfoutput>
<cfif currentRow gt 1 and getRows.customer_id[currentRow] getRows.customer_id[currentRow-1]>,&nbsp;</cfif>
	 #product_purchased#
	</cfoutput>
    </td>
</tr>
</cfoutput>

Open in new window

0
 
LVL 2

Author Closing Comment

by:rrattie
ID: 39327724
As you have done before, you made me think and pushed me in the right direction.

Always an excellent resource for developers who don't have anyone to bounce ideas off of in their own offices.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 39327950
Yeah, I was thinking you might need either a temp table or a (possibly) convoluted CTE ;-) Glad I could help.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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