Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

SQL - transposing & combining data from duplicate records

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
rrattie
Asked:
rrattie
  • 4
  • 4
1 Solution
 
_agx_Commented:
<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
 
rrattieAuthor Commented:
<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
 
_agx_Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rrattieAuthor Commented:
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
 
_agx_Commented:
[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
 
rrattieAuthor Commented:
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
 
rrattieAuthor Commented:
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
 
_agx_Commented:
Yeah, I was thinking you might need either a temp table or a (possibly) convoluted CTE ;-) Glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now