Let me ask, how often is this data updated? Your best bet might be to take a different approach from a simple select. You may want to pre-aggregate the data somehow before it is queried, for instance by creating a table to contain this information and updating it once an hour. You could even take a hybrid approach by storing aggregated data in another table and doing a UNION query to retrieve on the most recent records.
Otherwise, the format of a PIVOT table should look something like this: SELECT Group_ID, Supervisor_ID, SUM(CASE WHEN DATEPART(month,file_date) = 1) THEN number_jobs / number_employees END) AS 'January', SUM(CASE WHEN DATEPART(month,file_date) = 2) THEN number_jobs / number_employees END) AS 'February', ... FROM sometable M -----Original Message----- From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 2:17 PM To: CF-Talk Subject: OT: SQL data help. Hi all, I have data that I am working with some data that I need to (for lack of a better term) pivot. I have data that comes into a table in the format of: Group_ID, Supervisor_ID, Number_Employees, Number_Jobs, File_Date Example: Engineering, MGR1, 5, 6, 1-1-2003 Engineering, MGR1, 5, 9, 2-1-2003 Engineering, MGR2, 5, 6, 1-1-2003 Engineering, MGR2, 5, 9, 2-1-2003 Maintenance, MGR1, 5, 6, 1-1-2003 Maintenance, MGR1, 5, 9, 2-1-2003 Maintenance, MGR2, 5, 6, 1-1-2003 Maintenance, MGR2, 5, 9, 2-1-2003 >From this I figure the Rate (which is Number_Jobs / Number_Employees), so the table is actually: Group_ID, Supervisor_ID, Number_Employees, Number_Jobs, File_Date, RATE Example: Engineering, MGR1, 5, 6, 1-1-2003, 1.2 Engineering, MGR1, 5, 9, 2-1-2003, 1.8 Engineering, MGR2, 5, 6, 1-1-2003, 1.2 Engineering, MGR2, 5, 9, 2-1-2003, 1.8 Maintenance, MGR1, 5, 6, 1-1-2003, 1.2 Maintenance, MGR1, 5, 9, 2-1-2003, 1.8 Maintenance, MGR2, 5, 6, 1-1-2003, 1.2 Maintenance, MGR2, 5, 9, 2-1-2003, 1.8 My problem is that I have to display it like: Group_ID, Supervisor_ID, Jan_Rate, Feb_Rate, ..., Dec_Rate Example: Engineering, MGR1, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, 11.11, 12.12 Engineering, MGR2, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, 11.11, 12.12 Maintenance, MGR1, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, 11.11, 12.12 Maintenance, MGR2, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, 11.11, 12.12 Right now, I have it working, but it seems very inefficient. What I currently do is create a temp table in the format of the display. Then I insert the Group, Supervisor, and Jan Data. After that I do an update for each month. This is very slow and just looks wrong. Does anyone have any ideas how I might be able to do a pivot (still lack of a better word)? Steve *****COST SAVINGS SUGGESTION***** For pages that must be printed, change your print settings to print in "grayscale" instead of color. Steve Durette Mgr-Eng. & Const. Systems Support 100 S. Main Room 314 Mt. Clemens, MI 48043 Ofc: 586.466.7654 Fax: 586.466.1109 <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4