How to pull multiple fields into one query row?

[Chris rice]

Hi,

I'm trying to create a SQL query to output a Csv file for another application. The existing table is a list of line item purchases. So, if a single customer buys 3 items he would be listed in the table three times, once with each item.

How can I create the query to that a customer would only be listed once and each item purchased would be included in the same row.

Existing table example

Customer #. Name. Product. Order number
1. Smith, John. Widget 1. 1000.1
1. Smith, John. Widget 2. 1000.2
2. Doe, Jane. Widget 3. 1001.1


This is what I need

Customer #. Name. Product 1. Product 2. Product 3
1. Smith, John Widget 1. Widget2
2. Doe, Jane. Widget 3

I hope that makes sence.

Thank you,
Chris

[NetDynamic]

I am super tired haven't slept much this week but it should be something close to this:

SELECT * GROUP_CONCAT('Product' ORDER BY 'Product' ASC SEPARATOR '. ') AS 'Products' FROM 'table' GROUP BY 'CustomerNumber'

Your field names will differ but that's the basic idea I think...

You will definitely need to mess with the field names, and * is just there because I don't know what you have in there.

[NeoPa]

This isn't too straightforward a concept, which makes sense as it is contrary to normalised handling of data, intrinsic to any RDBMS. That said, it has been covered before (Combining Rows-Opposite of Union). I hope that this enables you to do what you require.

Be very careful of non-normalised (Database Normalisation and Table structures) designs though. They can appear to make life simpler, only to bite you in the bum later on.