MySQL Crosstab Query

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

About these ads

One thought on “MySQL Crosstab Query

  1. 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)
    ————————————

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s