[thelist] [SQL] Can I Do This?

[Jacques Capesius]

<snip>

> 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 | ....


</snip>

<snip wizard="Joshua L. Olson">

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
...
</snip>

Good idea, Josh.

This was kinda the way I was originally going to tackle this problem,
and the SQL to get output like that is pretty straight forward, so I'm
leaning towards this approach and doing some server side scripting to
massage it into what I want, as graciously suggested by Matt Warden the
other day. 

Another problem I ran into with this approach that isn't quite as easily
solved as the technical part, is the actual readability of a report that
would have potentially lots and lots of columns. 

My whole goal of laying out the data in this way is to view all these
monthly totals together for easy comparison among them, but when you
start getting a horizontal spread greater than, say, 10 columns, your
eyes are starting to have to cover a lot of ground to see all you want
to see.

So, I guess, in the final analysis, I may need to shelve this problem
for now until I can find a solution that is technically elegant and
produce output that wouldn't make Ed Tufte want to beat me up.

Thanks for all your help guys.