"Pivot" Query Display

[gatetec]

I have a SQL query for the output I need to have:

<cfquery .......>
select a.Dept, a.JobTitle, e.Shift, e.ExposureType, e.device, e.ExposureDate
from exposure e, AssociatePersonal a
where e.AssociateIDEx = a.AssociateID
</cfquery>


The output I need to have is the sample output at

http://www.mediafire.com/?9hzxddssiwy

I uploaded an Excel spreadsheet (qryoutput.xls) for better explanations.

Thx much

 

[r937]

can't read your excel file

please take the time to format it into plain text, and paste it here

r937.com | rudy.ca

 

[gatetec]

r937:

If you click on "Click here to start download..", you can view the file. Have you treid that?

thx

 

[peterswan]

You'll need a SQL query that's much better than you have. If you want to bring it down in one query, you'll need to visit the SQL forums to get the exact sytax. The final result will need to be ordered by Quarter, month, subCategory, and subCategoryCount. You can worry about the group totals in CF.

The CF output would be several <cfoutput group=""> nests with the group attribute set to quarter, month, and subCategory. You'll also need to use nested tables. I'd create one outer table, then a cell in the outermost CF group. Then, in that cell, start a new table and run the month output. Then, in the next group, start a new table and put the subCategory information with the group totals. Keep a running count of the category totals to get the group total. Then, as you end your groups, you can end the table, start a new cell, new table, and the end of the row, start a new row with the new month information. When the quarter is done, you can start over with a whole new row with a new table containing quarter information.

So, it's likely this can be done in one query, but if you prefer several queries that will also work, however, it will run a bit slower.

Keep in mind the SQL syntax will get complicated. You will need to create several new SQL generated columns and group by, order by these columns, i.e. months, sub Categories, and counts. With this layout you'll also need to create a whole new column named "Quarter", and also "SubCategory", which will be the name of each column, i.e. title, type, dept, etc.

Good luck,

Peter