Solved

SQL - transposing & combining data from duplicate records

Posted on 2013-06-26
8
280 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
[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
  • 4
  • 4
8 Comments
 
LVL 52

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 52

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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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 52

Accepted Solution

by:
_agx_ earned 500 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 52

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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