Solved

SQL - transposing & combining data from duplicate records

Posted on 2013-06-26
8
278 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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