SQL - transposing data from duplicate records

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.
Who is Participating?
gdemariaConnect With a Mentor Commented:
<cfoutput group="customer_id">
_agx_Connect With a Mentor Commented:
If you want commas in between, you'd need to adjust gdemaria's example slightly. One way is:

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

Open in new window

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

rrattieAuthor Commented:
Thank you both!  I had gone down the SQL path and was way over complicating the issue.
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.

All Courses

From novice to tech pro — start learning today.