Solved

SQL - transposing data from duplicate records

Posted on 2013-06-26
3
264 Views
Last Modified: 2013-06-26
Okay here's what I'm trying to do.

I have a table with the columns

customer_id product_purchased

When I query it comes out like:

1 product1
1 product2
1 product4
2 product3
2 product4
3 product3
3 product2

What I want is:
1 product1, product2, product4
2 product3, product4
3 product3, product2

Any thoughts?  I've been trying this via SQL and Coldfusion, but I'm stumped after 2 days.
0
Comment
Question by:rrattie
3 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 350 total points
ID: 39278687
<cfoutput group="customer_id">
  <br>#customer_id#
         <cfoutput>#product_purchased#</cfoutput>
</cfoutput>
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 150 total points
ID: 39278814
If you want commas in between, you'd need to adjust gdemaria's example slightly. One way is:

<cfoutput query="qry" group="customer_id">
  #customer_id# 
  <cfoutput>
       <cfif currentRow gt 1 and qry.customer_id[currentRow] eq qry.customer_id[currentRow-1]>,</cfif>
      #product_purchased#
  </cfoutput>
  <br>
</cfoutput> 

Open in new window


You could also do it in SQL with XML PATH. But using CF is the simplest

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28014813.html#a38837811
0
 
LVL 2

Author Closing Comment

by:rrattie
ID: 39278858
Thank you both!  I had gone down the SQL path and was way over complicating the issue.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now