A little help? Hard to explain.

[latemodern]

Hi,

I am trying to put an Invoices table together that is structured with the following columns:

Month | City | Venue | Product1Quantity | Product2Quantity | Product3Quantity | Product4Quantity

But I can only work out how to do this:

Month | City | Venue | ProductName | Quantity

...and multiple rows to show quantities for each individual product. The SQL is:

select (date_format( invoice_date,'%b')) as Month, city_name as City, venue_name as Venue, product_name AS Product, SUM(quantity) as Quantity
from invoices
inner join invoice_row on invoice_row.invoice_id=invoices.invoice_id
inner join product on product.product_id=invoice_row.product_id
inner join venue on venue.venue_id=invoices.venue_id
inner join city on city.city_id = venue.venue_city
where invoices.client_id =8 AND quantity != 0
Group by City, Venue, Month, Product
order by invoice_date, City

Is there any way of taking the product names and making them columns so that each invoice can appear on one line with quantities under each product?

Your help would be greatly appreciated.

[Fumigator]

It sounds like you are talking about formatting the data after you've selected from the database, which is a language-specific issue. What language are you using?

[latemodern]

Oh yes.

Its mysql. I am using Navicat to input the query, and export the results as an Excel spreadsheet (but that shouldn't make a difference should it?)

Thanks a lot

Chris

[guelphdad]

you would do this with your software used for formatting your output like php or coldfusion, not within mysql itself.

[raf]

Its mysql. I am using Navicat to input the query, and export the results as an Excel spreadsheet (but that shouldn't make a difference should it?)
why don't you create a pivot table in excel?