After wrangling with the data in python trying to effect a cross-tab result I finally stepped back and learned how to do it in MySQL. MS-Access has this nifty TRANSFORM … PIVOT function that was so handy I used to keep a copy of Access around just for this one function. (I even wrote a MS SQL Server / ColdFusion / ASP / VB / Access piece of horrible spaghetti code once just to use the PIVOT function within a web application once).
Any-hoo:
SELECT fake_user_name,
sum(if(week = 1, total_mins, 0)) as week1,
sum(if(week = 2, total_mins, 0)) as week2,
sum(if(week = 3, total_mins, 0)) as week3,
sum(if(week = 4, total_mins, 0)) as week4,
sum(if(week = 5, total_mins, 0)) as week5,
sum(if(week = 6, total_mins, 0)) as week6,
sum(if(week = 7, total_mins, 0)) as week7,
sum(if(week = 8, total_mins, 0)) as week8,
sum(if(week = 9, total_mins, 0)) as week9,
sum(if(week = 10, total_mins, 0)) as week10,
sum(if(week = 11, total_mins, 0)) as week11,
sum(if(week = 12, total_mins, 0)) as week12,
team_name, user_type, user_affiliation, user_tshirt, user_days_ex_week, user_did_getfit_before
FROM `final_2008_report-user_details`
group by fake_user_name,team_name, user_type, user_affiliation, user_tshirt, user_days_ex_week, user_did_getfit_before
I cannot get the following MYSQL Crosstab query to work, keeps erroring on the Convert Statement.
Question 1: How do I make my query below work with Dates?
Question 2: How do I convert your Crosstab query to work with Dates?
—————————————
SELECT tblCalixFilterMaint.cfmDate AS Tra_Date,
CONVERT(varchar(8),Tra_Date,1) AS ‘Day’,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 7 THEN 1 ELSE 0 END) AS ’7am-8′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 8 THEN 1 ELSE 0 END) AS ’8am-9′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 9 THEN 1 ELSE 0 END) AS ’9am-10′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 10 THEN 1 ELSE 0 END) AS ’10am-11′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 11 THEN 1 ELSE 0 END) AS ’11am-Noon’,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 12 THEN 1 ELSE 0 END) AS ‘Noon-1′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 13 THEN 1 ELSE 0 END) AS ’1pm-2′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 14 THEN 1 ELSE 0 END) AS ’2pm-3′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 15 THEN 1 ELSE 0 END) AS ’3pm-4′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 16 THEN 1 ELSE 0 END) AS ’4pm-5′,
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 17 THEN 1 ELSE 0 END) AS ’5pm-6′
FROM tblStatus RIGHT OUTER JOIN tblCalixFilterMaint ON tblStatus.statusID = tblCalixFilterMaint.cfmStatus LEFT OUTER JOIN tblColloNode ON tblColloNode.NodeID = tblCalixFilterMaint.cfmCollo
GROUP BY CONVERT(varchar(8),Tra_Date,1)
ORDER BY CONVERT(varchar(8),Tra_Date,1)
————————————