Solved

SQL - transposing & combining data from duplicate records

Posted on 2013-06-26
8
277 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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