Multiple lines on a chart?

Addendum: I do realise that if I have multiple columns each can be ascribed to a data series. But that would involve with the data I have doing a cross-tab which is not supported by mysql, and I cant do the #case trick because I dont know how many different sites there will be.

I am tempted ot convert to sql server which does support cross tabs, but I am not sure if asprunner will be able to handle them anyway so am reluctant to do this quite yet...

OK, I have created a workaround . Written an sql script that creates a new table as a cross tab with a column for any entries with a specific 'location'. I can then use this table, which is essentially a pivot/crosstab table, a ssource for the chart.

This script contains a union to link a current/archive sourcce table, and does a calculation as well on each row. Run as a cron job on the server.

-- Set sql_mode to ignore zero dates
SET sql_mode = 'NO_ZERO_DATE,NO_ZERO_IN_DATE';

-- Drop the table if it exists
DROP TABLE IF EXISTS inventory_crosstab;

-- Generate dynamic SQL for pivot
SET @sql_created = NULL;
SET @sql_location = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN SUBSTRING(location_name, 1, 3) = ''',
SUBSTRING(location_name, 1, 3),
''' THEN price * numberpacks END) AS ',
SUBSTRING(location_name, 1, 3) -- Use location_name substrings as column names
)
) INTO @sql_location
FROM (
SELECT location_name FROM inventory
UNION
SELECT location_name FROM inventory_archive
) AS combined_tables;

-- Create the new table inventory_crosstab
SET @sql_created = 'MAX(created) AS created';

SET @sql = CONCAT('CREATE TABLE inventory_crosstab AS SELECT id, ', @sql_created, ', ', @sql_location, ' FROM (SELECT FROM inventory UNION ALL SELECT FROM inventory_archive) AS combined_tables GROUP BY id;');

-- Prepare and execute the dynamic SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Reset sql_mode
SET sql_mode = '';

Only problem with this approach is that the charts are static intheir construction, each data item has to be allocated to a value in the query.

What would be great is if it could be dynamic, and allow all items in a collection each to be assigned a line, so that it can cope with an unknown number of 'columns'.