[thelist] [SQL] Can I Do This?

Matt Warden mwarden at gmail.com
Mon Jun 6 15:41:05 CDT 2005


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);


-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.


More information about the thelist mailing list