Is there any way to dynamically create columns non programatically? As in pure sql
queries?
This is what I am doing now:
SELECT SQL_BUFFER_RESULT
ORDERHEADER.ID_CUSTOMER AS 'Acct #',
CUSTOMER.Company AS 'Company',
SALESLEVEL.Name AS 'Client Type',
DATE_FORMAT(ORDERHEADER.AvailableAt, '%b') AS 'Month',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 1,1,0)) AS '1',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 2,1,0)) AS '2',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 3,1,0)) AS '3',
etc....
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 31,1,0)) AS '31',
COUNT(ORDERHEADER.ID) AS 'Total'
FROM ORDERHEADER
LEFT JOIN CUSTOMER ON ORDERHEADER.ID_CUSTOMER = CUSTOMER.ID
LEFT JOIN SALESLEVEL ON CUSTOMER.ID_SALESLEVEL = SALESLEVEL.ID
WHERE (ORDERHEADER.AvailableAt BETWEEN '[param:StartDate]' AND
DATE_FORMAT('[param:EndDate]', '%Y%m%d235959'))
GROUP BY ORDERHEADER.ID_CUSTOMER, DATE_FORMAT(ORDERHEADER.AvailableAt, '%m')
ORDER BY DATE_FORMAT(ORDERHEADER.AvailableAt, '%b'), Total DESC
(NOTE: [param:EndDate] and [param:StartDate] is filled in through a parser, these can
range from 1 day to months apart)
Sample output is as follows:
(these are days)
Acct# Company Client Type Month 1 2 3 ... 31 Total
3212 Joe's Bike Shop Weekly DEC 5 7 3 ... 4
72
3423 Sam's Deli Daily DEC 6 3 3 ... 2
65
3243 Jerry's Bi-Weekly DEC 4 5 2 ... 5
69
3212 Joe's Bike Shop Weekly JAN 0 4 3 ... 7
57
3423 Sam's Deli Daily JAN 0 2 7 ... 8
64
3243 Jerry's Bi-Weekly JAN 0 4 3 ... 3
57
Desired output:
Acct# Company Client Type 12/1 12/2 12/3 ... 12/31 12/Total
1/1 1/2 1/3 ... 1/31 1/Total Grand Total
3212 Joe's Bike Shop Weekly 5 7 3 ... 4 72
0 4 3 ... 3 57 129
3423 Sam's Deli Daily 6 3 3 ... 2 65
0 2 7 ... 8 64 129
3243 Jerry's Bi-Weekly 4 5 2 ... 5 69
0 4 3 ... 3 57 126
12/1, 12/2, 12/3 etc being month/day
The report original report has the day columns hard coded, can they by dynamic based
on the dates chosen, as in the number of days per month and the actual date numbers
themselves
Is this possible in any way? it doesnt matter if temp tables need to be made or not to
do this, whatever means sql queries can manage it
I have a progromatic (JAVA) way to do this with a while loop cycling through the date
ranges which generates the sql then runs that query, but don't really want to program
everything when a new report like this would be needed
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
