[thelist] [SQL] Can I Do This?

Jacques Capesius Jacques.Capesius at compellent.com
Mon Jun 6 10:51:23 CDT 2005


Hi folks,

Another wild and wacky SQL question for you wizards.

I'm working on providing reports against a CRM service database that
looks kinda like this:

Table: t_incident
-------------------------
incidentID (pk)
subjectID (fk)
timestamp

...which pulls the subject from a joined table that looks like...

Table: t_subject
--------------------------
subjectID (pk)
title

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

Getting each month its own row is the easy part. What I'm having trouble
with is making a sorta dynamic select list that would allow for
arbitrarily many subjects. Is there any way to do this within a SQL
Statement, or are we looking at me having to whip up a fancy-pants
stored procedure to handle something like this?

TIA!

-jacques :)



More information about the thelist mailing list