[thelist] [SQL] Can I Do This?

[Joshua Olson]

> -----Original Message-----
> From: Jacques Capesius
> Sent: Monday, June 06, 2005 11:51 AM
> 
> I'm looking to make a report that shows the number of 
> incidents, grouped
> by month and subject title, with each month as a separate 
> row, and each
> subject title as a column, so the output will be like this.
> 
> month 1 | subject 1 total | subject 2 total | subject 3 total | ....
> month 2 | subject 1 total | subject 2 total | subject 3 total | ....
> month 3 | subject 1 total | subject 2 total | subject 3 total | ....

Jacques,

Going back to the original question... I think this may be a case where you
may be asking a bit too much of the database.  Have you looked at solutions
where the result set contains  a record for each row AND each column and
then doing the formatting (ie, reorganizing the data back into a grid) in
the middle tier?

Example result set:

Month | Subject | Total
~~~~~~~~~~~~~~~
1 | Subject 1 | 100
1 | Subject 2 | 105
1 | Subject 3 | 110
1 | Subject 4 | 115
2 | Subject 1 | 89
2 | Subject 2 | 15
2 | Subject 3 | 92
2 | Subject 4 | 153
3 | Subject 1 | 59
3 | Subject 2 | 58
...

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/