[thelist] [SQL] Can I Do This?

Jacques Capesius Jacques.Capesius at compellent.com
Mon Jun 6 16:34:12 CDT 2005



-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Matt Warden
Sent: Monday, June 06, 2005 3:41 PM
To: thelist at lists.evolt.org
Subject: Re: [thelist] [SQL] Can I Do This?

Hi Jacques,

On 6/6/05, Jacques Capesius <Jacques.Capesius at compellent.com> wrote:
> 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?

You will need to know the subjects beforehand. You can do this in a
separate SQL statement or within your sproc. However, once you have
this information, it's not so bad. Something like this:

SELECT DatePart(mm,timestamp) as month,
SUM(CASE title
        WHEN 'subject1' THEN 1 ELSE 0
        END) as subject1sum,
SUM(CASE title
        WHEN 'subject2' THEN 1 ELSE 0
        END) as subject2sum,
SUM(CASE title
        WHEN 'subject3' THEN 1 ELSE 0
        END) as subject3sum,
SUM(CASE title
        WHEN 'subject4' THEN 1 ELSE 0
        END) as subject4sum,
...
SUM(CASE title
        WHEN 'subjectN' THEN 1 ELSE 0
        END) as subjectNsum
FROM t_incident i, t_subject s
WHERE i.subjectID=s.subjectID
GROUP BY DatePart(mm,timestamp)
ORDER BY DatePart(mm,timestamp);

Hi Matt,

Thanks for your input. Actually, that was the first solution that came
to mind, but I was leaning toward the approach I first postulated for a
few reasons.

1) There are over 20 distinct values for subjects, with the likelihood
for there being added a lot more, so I fear an approach like this would
have problems scaling.

2) There is a strong potential of subjects being added/deleted/changed
without my knowing, which could break the query without me initially
being aware.

Any thoughts / comments on how to tackle these two issues? One idea I
had was to group by month, and then by subject, which would allow me to
sum things just fine, but it would be harder to feed the resultset into
a graph object, which is one of my goals.

-jacques :)



More information about the thelist mailing list